Accumulate Values In The QlikView Data Model

When there is a need to count the same value against a number of dimension QlikView provides a number of ways of achieving this. Not all of them work entirely as you would like. This simple data model approach provides another way.

Common Solutions

Sometimes you need to accumulate values over a number of dimensions (perhaps time). QlikView has a tick box to allow this. With this tick box the values always accumulate from zero at the start of the chart. If you want to accumulate from a point before the start of the chart you can achieve this by adding the prior values on to the first point – using a bit of Set Analysis. Sometimes accumulations may inherently start some way back and not fully accumulate (i.e. a Moving Annual Total). In these cases you need to add onto the start and accumulate only a set number of steps back.

If you are looking for a value as at a single point then Set Analysis handle this very elegantly. By giving an explicit date selection including everything up to and including the max date you can accumulate everything before. Similarly, MATs can be obtained by providing a set of twelve months. Combining this type of expression with a date dimension in a chart becomes tricky though – as you can’t feed the dimension into your set.

The Problem

Whilst those work arounds have served me well in the past I was passed a question where neither of those approaches would work. The requirement was for a lending firm who wanted to track what the payback profile looked like for a number of loans. To be able to compare across loans they used Days Since Inception as a dimension and divided by the Total Loan Amount. As the payments were being accumulated the Accumulation tick box seemed like it would do the trick. This worked okay as long as the loan period was being calculated from day zero, but if a portion of the graph was selected then the accumulation started at the zero for the selected point, as shown in this chart:

There was also strange case where the accumulated figure was exceeding 100%, when a continuous axis was being used, as shown here:

What was needed was an approach that didn’t rely on the Accumulate feature. I turned to a solution I had used previously for MATs.

The Approach

What was needed was a way to count any pay back on Day Zero in every other day (we wouldn’t expect a value here). When counting Day One, this should be included in all days except Day Zero. Day Two would be there on all days except Days Zero and Day One – and so on. The last repayment made would have to only be counted once, on that final day itself. This could be approached in an expression – but the simple way is to have another dimension created for this purpose.

The Solution

All that was needed was an associated table that linked each elapsed day to each of the days before it. The required table needed to look like this (when limited to just the first few days):

The table could be created in the load script by having a nested loop for each of the number of days bringing in all of the numbers less than or equal to the current value.

The chart showing a subset of days looks like this, note the fact it does not start at zero:

The chart showing values for all time now accumulates to 100% rather than over 100%.

The change that needs to be made to the chart here is simply to turn off QlikView’s accumulation and use our accumulated dimension inserted of the previous dimension.

Other Uses

A similar approach can be used in other cases as well. I’ve already mentioned Moving Annual Totals, these allow the smoothing of monthly spikes to give a better idea of the trend of values. MATs can be created by building an associated table with twelve months attached to each actual month. Again a loop can be used to build this table:

If the resident load here is slow you could build a QVD of associated periods and bring this in with a WHERE EXISTS – but (to employ what I now call the Wunderlich principle) if it loads quick enough without doing this keep it simple and readable.

Another interesting conundrum that was solved using this technique was to a question raised by Alan Farrell – who wanted an average bar in his chart. By joining each value in a dimension to itself and then again to an Average dimension. In the chart with our new dimension all values appear in the one dimension – as shown here:

By applying an AGGR and an AVG function we can make that bar show the average – all other bars remain the same (as they are divided by one). Again, the dimension field changes for this chart to be one created by this code:

To get the average bar to stand out we need to use conditional colouring on the Expression:

=if([DebtorID With Avg] = 'Avg', rgb(0, 0, 130), null())

Conclusion

I’m sure there are many other ways this approach can be employed. Feel free to share below if you have an example. Hopefully with this technique in your kit bag you will be able to solve even more problems with QlikView.

Thanks Gysbert, I had missed that post and subsequent conversation when you originally posted it. A good read, especially as it fills the gaps regarding the alternative approaches that I just mention in passing above.

Hi Gonzalo, this is a nice use of a nested while and using the iterno() to pick out the value. The approach I used is clearer to read for giving the example, and has the advantage that you could execute other statements in the loop (perhaps setting variables for use in that iteration). I hadn’t realised that there would be a 5x difference in performance!

Regarding your script, you could make it a bit more compact by using AUTOGENERATE(100) and RowNo() to get the initial set. You can then do a WHILE and IterNo() on a preceding load, avoiding the RESIDENT load.

Steve your MAT solution is excellent and I have used the solution several times. However, I have been unable to to calculate the mat growth % in same chart. How would I calculate the growth rate for Nov-14 compared to Nov-13 showing in the bar for Nov-14. While only showing Mat Period Nov-14 thru Dec-13 in the chart? Any help would be appreciated. Thx – Nick

Hi Nick – glad you like the solution. Those kind of comparisons can be a bit tricky. You can use Set Analysis, but that is not aware of your dimensions – this leads you to have an expression for each period rather than a single expression and a period dimension. Using the extra MAT dimension you could do it by adding 24 months to each period, flag the first 12 as MAT1 and the other 12 months as MAT2. This kind of expression would then work:

I have just needed to solve a similar problem but instead of creating a separate date field I have calculated an MAT figure alongside each monthly balance. Then you can just swap between actual and MAT just by picking a different field. I loaded each balance line 12 times adding 0-11 to the sequential period ID (which does not have gaps). Then I totalled the balances by the amended period ID and mapped the answer back to the balance line with the matching period ID. You don’t have to worry about creating balances for future periods because they won’t map back to the original balance lines. See code below.

Best regards,

Chris

//**** First we need to load the balances 12 times, map on the period ID and add to it each time
for vMAT = 0 to 11
MAT:
LOAD
[MAT Actual],
[MAT Period ID],
[Account Code Combination]&’!’&[MAT Period ID] as [Link Field];
LOAD
*,
[Period Actual] as [MAT Actual],
[Period ID]+$(vMAT) as [MAT Period ID],//**** The period ID needs to be sequential without a gap between years. Month Year with add months would work also
[Account Code Combination]

Hi Chris. I have done similar to this in the past, and it works well and is robust. Loading all of the data many times may take a while if there are lots of rows though. There will only be a finite number of dates in the data model (many fewer months – if you go to that granularity) so the join table will be relatively small and quick to create. The approach you give here would be good in a QVD generate, where the stored QVD would have both single month and twelve month values on each row. This could then be handed to someone to build their analysis on and they would not need to worry about joining, grouping or any of those kinds of things.

Hi Steve, I have found a problem with my idea of mapping the data back to the balances because if there is no balance posted in a period there may still be an MAT for that period. So I will have to outer join or concatenate the MAT lines onto the balances table instead.
As for speed, with the data set I am working on, 3 years of MAT balances generate in about 30 seconds which is fine.
Chris