Result Sets and Display Operations
There is sometimes a difference between the results that need to be analyzed and the results that should be displayed. BQL separates those two concepts. The elements listed above define a slice of the overall logical set of information that is available to be analyzed. Based on that selection, you might want to analyze it and return a subset using display operations. These operations control the “display” of the results generated from the sub-set that has been specified.
In short the difference between regular data operations and display operations is this:
- Data operations are carried out at the database level and return results that meet the criteria.
- Display operations are carried out after the database returns a result set for the report query.
Display operations include DISPLAY WHERE, DISPLAY BY, RANK, PTILE (percentile calculations) and expressions.
- Filtering Results - DISPLAY WHERE
- Display Ordering - DISPLAY BY
- Ranking Calculations - RANK
- Percentile Ranking Calculations - PTILE
- Ranks with Breaking Attributes
- Nested Ranks and Percentiles
Filtering Results - DISPLAY WHERE
One basic display operation is the display filter, DISPLAY WHERE. Just like a normal dimension column or measure WHERE filter, you can filter on given columns:
DISPLAY WHERE [Products.Categories]='Desktops'
DISPLAY WHERE [Order_Date: Sum: Quantity]>=100000
Other than the column names specified, there is no difference between a display filter on measure columns and dimension columns. This is because the grain of the result set is already determined by the definition of dimension columns in the query. Hence, for a display filter to work properly, the column must be included in the select portion of the logical query. Display filters might seem redundant, but they are quite powerful when used in conjunction with other display functions. Also by controlling and leveraging caching, display filters can result in significant performance improvements.
You can combine display filters using ANDs and ORs.
SELECT [Products.Categories], [Products.Products], [Time.Year/Quarter], [Order_Date: Sum: Revenue] FROM [ALL] DISPLAY WHERE [Time.Year/Quarter]='2014/Q1' AND ([Order_Date: Sum: Revenue]>=10000000 OR [Products.Categories]<>'Desktop') ORDER BY [Order_Date: Sum: Revenue] DESC
Display Ordering - DISPLAY BY
Display ordering is like ORDER BY, it sorts by attribute orderings or measure orderings. However DISPLAY BY sorting is applied after the result set has been generated and any other display operations have been performed.
DISPLAY BY [dimension.attribute]
DISPLAY BY [date_type: aggregation: measure]
Ranking Calculations - RANK
In addition to calculating various business measurements, it is often useful to understand how those measurements relate to an overall population. You can rank a measure based on its value versus the result set created as an extension of specifying a measure. For example, this query lists each product category and its rank in terms of revenue:
SELECT [Products.Categories], RANK([Order_Date: Sum: Revenue]) FROM [ALL]
| Categories | RANK([Order_Date: Sum: Revenue]) |
| Desktops | 3 |
| Mobile Phones | 4 |
| MP3 Players | 5 |
| Notebooks | 2 |
| Tablets | 1 |
In this example the tablets have the highest revenue therefore the rank is 1.
Using RANK With or Without a Display Filter
Display filters can affect the results of a RANK operation. For example, without a display filter:
SELECT [Products.Categories], RANK([Order_Date: Sum: Revenue]) FROM [ALL] WHERE [Time.Year]=2014
| Categories | RANK([Order_Date: Sum: Revenue]) |
| Desktops | 4 |
| MP3 Players | 5 |
| Mobile Phones | 3 |
| Notebooks | 2 |
| Tablets | 1 |
The query without a display filter creates a result set that includes all product categories for the year 2014 and then ranks each product category in terms of unit sales.
With a display filter, the sort column must be included in the SELECT.
For example:
SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue]) FROM [ALL] DISPLAY WHERE [Time.Year]=2014
|
Year *Note |
Categories | RANK([Order_Date: Sum: Revenue]) |
| 2,014 | Desktops | 13 |
| 2,014 | Mobile Phones | 11 |
| 2,014 | MP3 Players | 20 |
| 2,014 | Notebooks | 10 |
| 2,014 | Tablets | 2 |
Note: The Query Admin - Queries tab displays Time.Year as integers.
The query with a display filter considers the revenue for all product categories for all years and ranks the unit sales for those products by year across all time, and presents only the results for 2014.
The second query gives the rank of 2014 sales for the product category across all time, and the first one ranks only within 2014.
Using RANK with Display Ordering
You can sort or order results based on the rank, using display ordering.
SELECT [Products.Categories], RANK([Order_Date: Sum: Revenue]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]='2014' DISPLAY BY [Revenue Rank] ASC
| Categories | Revenue Rank |
| Tablets | 1 |
| Notebooks | 2 |
| Mobile Phones | 3 |
| Desktops | 4 |
| MP3 Players | 5 |
The display order is applied to the Revenue Rank column.
Percentile Ranking Calculations - PTILE
PTILE functions just like RANK, only it returns the percentile rank of a value (rank vs. size of population).
SELECT [Products.Categories], PTILE([Order_Date: Sum: Revenue]) FROM [ALL] WHERE [Time.Year]=2014
| Categories | PTILE([Order_Date: Sum: Revenue]) |
| Desktops | 0.2 |
| Mobile Phones | 0.4 |
| MP3 Players | 0 |
| Notebooks | 0.6 |
| Tablets | 0.8 |
As with RANK, you can use DISPLAY WHERE and DISPLAY ORDER with PTILE calculations. Remember to create a column name so that the display filter column is in the SELECT column list.
SELECT [Products.Categories], PTILE([Order_Date: Sum: Revenue]) 'Percentile Revenue' FROM [ALL] WHERE [Time.Year]=2014 DISPLAY BY [Percentile Revenue] ASC
| Categories | Percentage Revenue |
| MP3 Players | 0 |
| Desktops | 0.2 |
| Mobile Phones | 0.4 |
| Notebooks | 0.6 |
| Tablets | 0.8 |
Ranks or Percentile Ranks with Breaking Attributes
You might also want to calculate ranks or percentiles at a finer grain – grouping by some other result in the query. For example if you wanted to rank products within each year and show two years:
SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue] BY [Time.Year]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]=2013 OR [Time.Year]=2014 DISPLAY BY [Time.Year] DESC, [Revenue Rank] ASC
|
Year *Note |
Categories | Revenue Rank |
| 2,014 | Tablets | 1 |
| 2,014 | Notebooks | 2 |
| 2,014 | Mobile Phones | 3 |
| 2,014 | Desktops | 4 |
| 2,014 | MP3 Players | 5 |
| 2,013 | Tablets | 1 |
| 2,013 | Notebooks | 2 |
| 2,013 | Mobile Phones | 3 |
| 2,013 | Desktops | 4 |
| 2,013 | MP3 Players | 5 |
Note: The Query Admin - Queries tab displays Time.Year as integers.
You could also use a display filter to narrow down the results (similar to TOP). For example:
SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue] BY [Time.Year]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]=2013 OR [Time.Year]=2014 DISPLAY WHERE [Sales Rank]<=3 DISPLAY BY [Time.Year] DESC, [Revenue Rank] ASC
| Year | Categories | Revenue Rank |
| 2,014 | Tablets | 1 |
| 2,014 | Notebooks | 2 |
| 2,014 | Mobile Phones | 3 |
| 2,013 | Tablets | 1 |
| 2,013 | Notebooks | 2 |
| 2,013 | Mobile Phones | 3 |
Nested Ranks and Percentiles
BQL also supports nested RANK and PTILE calculations to do ranks of rank calculations. For example:
RANK(RANK([Order_Date: Avg: UnitPrice]+10)+RANK([Order_Date: Sum: Quantity]))
RANK(RANK([Order_Date: Avg: UnitPrice]+10)+RANK([Order_Date: Sum: Quantity BY [Products.CategoryName]]))
See Also