A drill-down table lets you step through a hierarchy of grouped and summarized data to view varying levels of detail. To enable this feature, the drill-down table must be built so that each column rolls up correctly. This process is usually straightforward, as numbers are aggregated (summed, averaged, etc.) by their grouping. However, in certain cases, such as when aggregating ratios, formulas are required to ensure the data aggregates correctly at each level.
This article includes basic and advanced drill-down table information and examples. For an introduction to drill-down table configuration, go to this article.
Important notes about drill-down table configuration:
- All table columns must contain the same number of items and be aligned across rows.
- Only columns formatted as “Text” or “Date/Time” can be grouped.
- Only columns formatted as “Number”, “Currency”, “Percentage”, “Duration”, “Mini Chart: Sparkline” or “Mini Chart: Spark Bar” can have arithmetic actions (SUM, AVERAGE, MIN, MAX) applied to them.
- Custom results rows do not automatically recalculate as the user drills down through the levels.
- “Filter”, “Group”, “Aggregate”, and “Sort” actions are applied to the full set of data, before the drill-down levels are grouped.
- Sort results are accurate at the last ungrouped level.
- “Group” and “Aggregate” are typically not needed because drill-down functionality performs grouping and aggregation.
This article includes:
Standard table: Example
Each row in the following standard table contains data for every column. The first 6 columns are pulled directly from the data source. The “Revenue/Sales” and “Revenue/Population” columns are straightforward calculations: &Column: Revenue / &Column: Sales and &Column: Revenue / &Column: Population respectively.
Basic drill-down table: Example
The numeric data in the standard table above can be aggregated according to groupings defined by the text fields: “Country”, “Province/State” and “City”. In the following basic drill-down table example, only the “Population”, “Revenue”, and “Sales” columns are used.
Configuring a basic drill-down table
To define the drill-down path:
- In the Klip Editor, edit a standard table Klip.
- Select Table in the component hierarchy.
- Open the Drill Down tab and select Enable drill down to begin grouping the data.
- At Group by, use the drop-down to select the text column to group by. This will be the highest level grouping.
- Click Configure other columns at this level and select how to handle the data in the remaining columns:
- Text and Date/Time column options include Hide, Count, or Count Distinct.
- Numeric column options include Hide, Sum, Average, Count, Count Distinct, Min, or Max.
- When a Group by level is selected, the Add Drill Level button is displayed until the final text column is reached. To show all rows, leave the final level ungrouped by setting Group by to blank.
Tip: The maximum number of drill levels, including the last ungrouped level, equals the number of text columns in the table. To configure a drill level for every text column and also leave the last level ungrouped to show full details, add an extra hidden column (not hidden data) and set the formula to reference the lowest level grouping (in this example,&Column: City). - When you’re finished adding drill levels, click Save or Save and Exit.
Manipulating data with a formula
In the standard table example (above), the “Revenue” and “Sales” columns contain unique entries per row which can be aggregated as-is. However, the “Population” column gives the total population for the city in each row. This data must be manipulated to aggregate correctly when drilling down through the “Country”, “Province/State”, and “City” levels.
To do this, the population must be divided by the number of times the city appears in the data using a formula like:
Population / LOOKUP( City, GROUP(City), COUNTDISTINCT(City) )
For example, the standard table has 2 rows for Ottawa:
Canada ON Ottawa 1,236,324
Canada ON Ottawa 1,236,324
In the basic drill-down table, the population for Ottawa must be divided by 2 so that it sums correctly at the city level and higher.
Canada ON Ottawa 618,162
Canada ON Ottawa 618,162
Viewing drill-down tables on a dashboard
Add the drill-down table Klip to a dashboard to view it.
Click an underlined blue link to drill down to the next level and click Back to return to the previous level.
Advanced drill-down table: Example
In this advanced drill-down table example, all the numeric columns in the standard table (Population, Revenue, Sales, Revenue/Sales, Revenue/Population) will be aggregated according to groupings defined by the text fields: “Country”, “Province/State” and “City”. This example builds on the steps described in the basic drill-down configuration section.
To sum ratios such as “Revenue/Sales” and “Revenue/Population” requires separately summing the numerator and the denominator, that is:
SUM( &Column: Revenue ) / SUM( &Column: Sales )
SUM( &Column: Revenue ) / SUM( &Column: Population )
A unique formula is required for each level of the drill-down table since the denominator must be grouped differently for each level (the numerator will be summed as is).
For example:
Toronto revenue/sales = (75000 + 643000) / (643 + 235) = 75000/878 + 643000/878 = 817.77
Ottawa revenue/sales = (867000 + 4343000) / (111 + 178) = 867000/289 + 4343000/289 = 18027.68
ON revenue/sales = (75000 + 643000 + 867000 + 4343000) / (643 + 235 + 111 + 178) = 75000/1167 + 643000/1167 + 867000/1167 + 4343000/1167 = 5079.69
To do this requires a table column for each level, where:
- Revenue/Sales (country)
D:D / LOOKUP( A:A, GROUP( A:A ), GROUPBY( A:A, E:E) )
- Revenue/Sales (province/state)
D:D / LOOKUP( CONCAT( A:A, B:B ), GROUP( CONCAT( A:A, B:B ) ), GROUPBY( CONCAT( A:A, B:B ), E:E) )
- Revenue/Sales (city)
D:D / LOOKUP( CONCAT( A:A, B:B, C:C ), GROUP( CONCAT( A:A, B:B, C:C ) ), GROUPBY( CONCAT( A:A, B:B, C:C ), E:E) )
On the Drill Down tab, for each level, sum the applicable column and hide the others. (See below.)
