AX1117
You can organize your Axiom query data into ranked segments, so that you can easily see which records fall into the top, middle, and bottom of the data distribution.
For example, you may want to construct an Axiom query that returns the revenue for each store in Q1. Then you want to rank each store based on where they fall in five segments, where segment 1 contains the top performers and segment 5 contains the bottom performers.
Defining the segment parameters in the field definition
To segment your Axiom query data, you use special syntax in the Axiom query field definition. This syntax defines the number of segments and the database column to use for segmentation, and creates a special "virtual" column to report on the segment results.
The segment syntax is as follows:
[Column=VirtualSegmentColumn; Type=Segment; Segments=Number; SegmentBy=ColumnName]
| Parameter | Description |
|---|---|
| Column |
The name of the virtual column to report the segment results. You can name this column anything you like—for example, you could name it Segment (for any number of segments) or Decile (if you are using 10 segments). When the query is run, this column will return the number of the segment that each data record in the query belongs to. |
| Type |
Use the keyword |
| Segments | The number of segments to organize the data into. Enter any whole number greater than 1. For example, if you want the data divided into 5 segments or 10 segments, enter 5 or 10. |
| SegmentBy |
The database column(s) to be used as the basis of the segmentation. At the database level, this will sort the query data using the SegmentBy column(s), and then the segment rankings will be assigned based on this order. For example, if you want the records to be segmented based on their results in Q1, enter the column name Q1. The column name can optionally be followed by You can use column names and alias names in this setting. If you use a column name, the primary table is assumed if the table name is omitted. Values in the SegmentBy column(s) are evaluated using ascending order by default, which means that records with the lowest values will be in the "top" segment. If instead you want the records with the highest values to be in the top segment (such as in the example of segmenting based on revenue), then you should specify descending order. |
In the previous example of segmenting stores by Q1 data, the field definition syntax would be as follows:
[Column=Segment; Type=Segment; Segments=5; SegmentBy=Q1 Desc]
The "sum by" for the Axiom query would be set as appropriate to return stores as the rows. For example, the sum by might be set to Dept.Dept with a data filter to restrict the results to departments that are stores. The data filter for the query would also be used to restrict the results to revenue.
Limitations and design considerations
Because the virtual segment column is not a real database column, it cannot be used in most "regular" Axiom query settings such as the sum by or the data filter. There is one exception—the column can be used for the database sort.
-
Sorting by segment: If you want to sort the Axiom query results based on the segment numbers, you can use either the database sort or the spreadsheet sort. For the database sort, use the virtual column name as defined in the field definition syntax (for example,
Segment). -
Grouping by segment: If you want to group the Axiom query results based on the segment (i.e. each row shows the sum of a particular segment), then you must use the Post-Query Sum By option in the Query Details section of the Control Sheet. Enter the virtual column name as defined in the field definition syntax (for example,
Segment). The regular "sum by" setting does not recognize the virtual column and cannot use it to group results. -
Filtering by segment: If you want to filter the Axiom query results by segment—for example, only show departments that fall into segment 5—then you can use the Post-Query Filter option in the Query Details section of the Control Sheet. When defining the filter, use the virtual column name as defined in the field definition syntax, such as:
Segment=5. Alternatively, you can filter by segment using data range filters—for example, to have one data range per segment, such as[aq1; segment=5]. The Axiom query data filter and the sheet filters do not recognize the virtual column and cannot use it to filter results. -
Drilling: Segmenting is not supported for use with drill-down drilling. For example, if you are using the post-query "sum by" to group by segment, you cannot drill down to the dimension elements in that segment. If you need to drill segmented data, you may be able to configure a custom drill to meet your needs.
