In reporting, we are all familiar with the need for totals and subtotals. In tools like Qlikview, it is easy to add levels of subtotaling to a given set of dimensions in a pivot table. We simply choose the dimension level and check a box for “Show Partial Sums”. In Qlikview, subtotaling options are applied across all expressions. In specials cases, we may want to customize subtotaling for each expression.
Let’s consider the following scenario. We have a need to chart both Sales and Days Outstanding together over multiple common dimensions like Department, Customer, and Purchase Order. Summation metrics like Sales are relevant at all levels, however Days Outstanding may not have meaning when aggregated over Department or even Customer.
Here is the spreadsheet I’m using, if you’d like to follow along.
We’ll need to establish todays date in a variable. Set a variable named vToday to the following value: 3/15/2011. This variable will be used for the Days Outstanding expression.
Next, we’ll create our pivot table and add dimensions for Department, Customer, and Purchase Order.
Add the following expressions:
- Sales – SUM(Sales)
- Invoice to Payment – AVG(Paid_Date–Invoice_Date)
- Days Outstanding – AVG(IF(vToday >= Due_Date, vToday–Due_Date))
We want to see sales subtotaled at all levels, so check “Show Partial Sums” for each dimension on the Presentation tab.
For this scenario, lets assume the following: 1) “Invoice to Payment” is only relevant over the Department and Customer levels. 2) “Days Outstanding” is only relevant at Purchase Order Level .
You’ll notice that when using a pivot table versus a straight table, “Total Mode” is grayed out on the expression tab. In order to achieve customization at the expression level, we’ll have to alter our expressions and introduce the dimensionality function.
The Dimensionality function lets us identify the depth of the aggregation. To familiarize yourself with its use, add dimensionality() as an expression to a chart with the same dimensions as your working chart. Note: You’ll want to uncheck the “suppress zero values” option. The resulting chart will look like this.
When we examine this along with the business rules, we can say that we only want to show “Invoice to Payment” when dimensionality is greater than zero, and “Days Outstanding” when dimensionality is equal to three.
We’ll make the following adjustments to our expressions:
- Invoice to Payment – IF(Dimensionality() > 0,AVG(Paid_Date–Invoice_Date))
- Days Outstanding – IF(Dimensionality() = 3,AVG(IF(vToday >= Due_Date, vToday–Due_Date)))
Our final product illustrates subtotal suppression using the dimensionality. If anyone has any creative uses for the dimensionality function, I’d love to hear about them.