Qlikview Subtotaling Customization

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.

Sales.xls

 

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.

About these ads
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

One Response to Qlikview Subtotaling Customization

  1. Nate says:

    Brittany,
    Very nice article and great info. I just started with Qlikview in February and stuff like this really helps a lot. Your example is great and well laid out.

    I was actually searching for a solution to a different problem and was wondering if you had any thoughts.

    I have a pivot table I am working on for “Operating Revenue”. It has ‘Categories’ along the left, which consist of ‘Gross Patient Revenue’, ‘Discount’, and ‘Other’.

    Along the top, these are pivoted by Ledger Type, so ‘Actuals’, ‘Budget’, and ‘Variance’.

    My question is…I am looking to have a “subtotal” row in there called “Net Patient Revenue”, which is the sum of Gross and Discounts. However, the “Total” Operating revenue should just be Gross + Discount + Other, and the new “Net” calculation should not factor into it.

    Do you know of any way to do this? Have you come across anything like it?

    Thanks,
    Nate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s