It’s Technical: Using Calculated Dimensions

 

As one of Wipfli’s QlikView developers, I was recently given the task to identify and analyze the Top 10 customers for a client of ours. Initially I thought, “easy enough”. If this client defines its top customers by sales, then I would simply, as an example, create a straight table chart object applying Customer as the dimension, sum of Sales as the expression, sort the table by Sales in descending order, and then limit the dimension values to 10 by using the Max Number option. Done, right? Well, not so fast my friend…there’s a twist. It turns out that this particular client defines its Top customers by Sales (yes), but from last year. This helps ensure that decisions made this year are only directed towards active customers. Since this client’s data set includes many years of sales data, it is possible that if you were to get a list of the Top 10 customers, it would include customers from several years back that are simply not current customers today.

 

My goal then was to create a straight table chart object that would capture and display the Top 10 customers (defined by sales from last year) without having a QlikView user make any selections in any available list boxes. I quickly came to the conclusion that the key to this castle was in creating a calculated dimension.

 

Here’s how I built my straight table chart object to capture the Top 10 Customers:

 

Chart Expression
Let’s get the easy part out of the way. My one chart expression, Sales, was simply calculated like so:

 

Sum (Sales)

I created a Calculated Dimension to be the following:

 

Let’s dissect this formula from the inside out to understand it better.

 

Sum( {$<Year={$(=Max( {1} total Year)-1)}>} Sales)
Returns sales for the prior year, disregarding any selection in Year made by the user.

 

Rank(Sum( {$<Year={$(=Max( {1} total Year)-1)}>} Sales))

Returns a ranking of the sales values from highest to lowest. The highest ranking (lowest ranking number) is determined by the highest sales amount.

 

Aggr(Rank(Sum( {$<Year={$(=Max( {1} total Year)-1)}>} Sales)), Customer)
Returns the ranked sales values calculated over a dimension which is Customer.

 

If(Aggr(Rank(Sum( {$<Year={$(=Max( {1} total Year)-1)}>} Sales)), Customer) <= 10, Customer)
Finally, using a simple If function returns only the top 10 ranked customers .

 

Note: Be sure to select the Suppress When Value is Null checkbox in the Dimension settings.

 

My Top 10 Customers chart object is then visualized like so:

 

 

This chart displays Sales by Customer for all years loaded, but is only capturing the Top 10 Customers, defined by the 10 highest ranking customers by Sales of last year.

 

Our client can now quickly visualize its Top 10 customers (based on sales of last year) without having to make any selections in any available list boxes. On the flip side, our client can also conduct interactive analysis by making list box selections to filter the data by other attributes, like Country, Division, etc.

 

Hope this was helpful. Until next time!   Scott