Function With Flexible, Choosable Dimensions

I worked on a document last week that calculated a performance indicator metric based on budget amounts compared to actual spending amounts. The chart expression for the calculation used the Aggr function and two dimensions: spending program and geographic region. Simplifying from the actual expression, it looked something like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), SProgram, SRegion))

The business requirement had changed and I needed to provide a method so the person using the document could choose the level at which to calculate the differences and the level could involve one or two different fields and a choice of weekly or monthly time period.

I decided to use a method that would eliminate the need for if statements in the expression and also provide an easy way to add more choices in the future. First, I built an inline load of the choices in the loadscript (a data island since the field names do not appear in any other table):

CALC_LEVEL:
Load * Inline
[CALCLVL_DESC : CALCLVL_CODE
Program, Region, Weekly : SProgram, SRegion, WEEK_start_date
Program National, Weekly : SProgram, WEEK_start_date
Region, Weekly : SRegion, WEEK_start_date
Program, Region, Monthly : SProgram, SRegion, Fiscal_period445
Program National, Monthly : SProgram, Fiscal_period445
Region, Monthly : SRegion, Fiscal_period445
] (delimiter is ':');

Note that I specified a colon as the field delimiter so that I could include commas in the field values of CALCLVL_DESC.

In the document, I created a Listbox for the CALCLVL_DESC field and titled it “Please choose a calculation level:” So, the business user can see the possible choices using familiar words that make sense in a business context. The listbox was configured for Always One Selected Value. When the user makes a selection of CALCLVL_DESC it means that there is only one available value of CALCLVL_CODE which is important for making the expression work correctly.

The dimension part of the Aggr function was changed so that the expression now looks like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), $(=CALCLVL_CODE) ))

That makes the dimension part of the Aggr function take on the specific field names listed in the CALCLVL_CODE value. It allows the user’s choice to automatically control the fields used in the dimension. Adding new choices in the future would be a matter of adding new lines to the inline load.

This is a powerful technique, it is not limited to the Aggr function or an inline load data island. It can replace an entire expression or any part of an expression based on the current selection (as you read that sentence, keep in mind how flexible and powerful the concept of selection is for QlikView).   

★★★