Non Comparative Dates Image

Extreme Synthetic Dimensions in QlikView


Mixing ValueLoop, ValueList and Parametarised Variables to create the mother of all synthetic dimensions

I’ve seen quite a few posts recently about the use of QlikView’s Dollar Sign Expansion using parameters and the use of the ValueLoop() and ValueList() functions.  I thought, for my first post I would demonstrate a real world example combining all of these techniques using a synthetic dimension.

Recently for a project I was working on, I needed to find a way of grouping data from two alternate date states into a single table.  The date periods would always be in comparable buckets (ie, weeks or months) but the application made it possible to compare any date range with any date range. This flexibility ruled out standard set analysis flags for comparing dates as it was not possible to pre flag associated ranges as demonstrated in Richard Pearce’s excellent Date Comparison Calendar.

Non Comparative Dates Image
The finished product.  Column one of the table compares the first periods of the two charts and so on.

Creating a Synthetic Dimension

The approach I took to solve this was to use a synthetic dimension.  A synthetic dimension is nothing more than one that didn’t exist in the data when it was originally loaded.  QlikView gives us two functions to play with to create synthetic dimensions. The ValueList() and the ValueLoop(). On the surface, these are 2 straightforward functions but don’t be fooled; they are both powerful functions in their own right but their usage and flexibility is not widely appreciated. To create the dimensions for this table, I started with the ValueList() function. In its most basic form, it can be used as such:

ValueList('Month 1', 'Month 2', 'Month 3') // etc etc

Which will give you a chart with 3 dimensions called Month 1, Month 2 and Month 3. However, we can take the creation of the synthetic dimension a step further by nesting a ValueLoop() within the ValueList(). This way, the synthetic dimension can be generated dynamically, based on our data:

LET mySynthDimension = Concat(chr(39)&'Month '&Valueloop(1,$(DimensionCount))&chr(39), ',',Valueloop(1,$(DimensionCount)));
// The DimensionCount variable would be based on a count of another *real* dimension, eg getPossibleCount(Months)

Note, we’ve wrapped our ValueLoop() in a concat() function.  This has the effect of flattening the output of the loop into a text string, ready to be used within our ValueList().  This code in turn is saved in a variable called mySynthDimension which we can call from within the ValueList() when we are ready to use it.

Creating a Synthetic Dimension in the Create Dimension Dialogue Box
Adding our synthetic dimension in the Chart Dimensions dialogue box.

So, hopefully you are still with me. If you’ve gotten this far, you’re more than half way as we use the same technique with the ValueLoop() and ValueList() to create the expression. Stay tuned, the really cool stuff happens next.

Creating the expressions

The chart type we use for this table display is the Straight Table with the Horizontal option checked under the Presentation tab.  This in effect pivots the table so our date rows are presented across the top (Month 1, Month 2 etc) as columns and our expressions are shown as rows.  In order to show the figures as we want them in the table, we need to create two expressions.  One for the buckets in the top chart and one for the buckets in the bottom.

The buckets are in fact dates, and it just so happens that we have a date serial in our calendar dimension.  A date serial is just a sequential list of numbers corresponding to each of your dates in order.  This is handy as it allows you to add 1 to a date to get the next day/week/month/whatever and not worry about cumbersome date arithmetic.  To that end, our expression would look a bit like this:

 SUM({$<WeekSerial={'$(=Min(MonthSerial))'}>}Sales) // Current Period
 SUM({[Prior]<WeekSerial={'$(=Min(MonthSerial))'}>}Sales) // Prior Period

These expressions take the lowest possible date for the two date states (the Current and Prior period) and display them in the table. This won’t work quite yet though for two reasons. Firstly, it’s only ever going to show the value for the lowest selected date and secondly, seeing as we have built a synthetic dimension, we need a way of telling QlikView how to bucket these expressions into the dimensions we created.

Assigning the totals to the correct synthetic dimension

The documentation for ValueList() suggests a very cumbersome way of assigning your metrics to the correct dimension. Basically, it has you rewriting the code for your synthetic dimension multiple times to test for each scenario. In our example if would look a bit like this:

 IF(ValueList('Month 1','Month 2','Month 3') = 'Month 1'
   ,'In Month 1 bucket'
   ,IF(ValueList('Month 1','Month 2','Month 3') = 'Month 2'
     ,'In Month 2 Bucket'
     ,IF(ValueList('Month 1','Month 2','Month 3') = 'Month 2'
     ,'In Month 3 Bucket'

…which is a bit cumbersome I have to admit. However, there is a better way that makes your code a whole lot more manageable and opens up a world of possibilities. Enter the well loved and practically inseparable Pick(Match()) functions:

  match('Month '&RowNo(),'Month 1','Month 2','Month 3')
  ,'In Month 1 Bucket','In Month 1 Bucket','In Month 1 Bucket')

As you see, the code is significantly more compact and readable. This technique introduces the RowNo() function which returns effectively returns the row number or dimension bucket the code is in. The pick(match()) combo is also a lot more performant than doing a nested IF() so use it wherever you can.

Making it dynamic, the Parameterised Dollar Expansion

Now we have a dimension and a couple of expressions we can use, it’s time to introduce the dollar expansion with parameters. In this way, we can create the closest thing to a client side function that Qlik allows. So, lets take the expressions we created earlier and put them into a couple of variables:

 SET vCurrentPeriodSales = 'SUM({$<WeekSerial={"$(=Min(MonthSerial)+$1)"}>}Sales) // Current Period
 SET vPriorPeriodSales   = 'SUM({[Prior]<WeekSerial={"$(=Min(MonthSerial)+$1)"}>}Sales) // Prior Period

Note the inclusion of the $1 in the code. This allows us then to pass data into our variable as it is executed. So we can do something like this:

 =$(vCurrentPeriodSales(24))  // would run an expression like this SUM({$<WeekSerial={"$(=Min(MonthSerial)+24)"}>}Sales)

If we now reintroduce our ValueLoop() and ValueList() functions we can build a completely dynamic expression which responds actively to user selections:

      'Month '&RowNo()
      $(=Concat(chr(36)&'('& vCurrentPeriodSales &'('&Valueloop(0,DimensionCount)&'))', ',',Valueloop(0,DimensionCount))

When the code runs, it produces something like this:

      'Month '&RowNo()
      ,'Month 1','Month 2','Month 3'

…which finally matches the calculations to the correct synthetic dimension.

A short note on performance

This is a succinct and easy to maintain solution to a specific problem and in my tests, it produced more than adequate performance. However, because the expression effectively changes for each dimension bucket it calculates against, it will not be as fast as working with a standard dimension and expression, it may however, get you out of a tight spot though.

Leave a reply

Leave a Reply

Your e-mail address will not be published. Required fields are marked *