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:

Days27to44Accumulation

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

Accumulate To Over 100 Percent

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):

QlikView Mapping Table

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.

First we peek out the longest duration for a payback:

Temp_MaxDays:
LOAD
     max([Repayment Day]) as MaxDays
RESIDENT Repayments;

let vMaxDays = peek('MaxDays', 0, 'Temp_MaxDays');

Then the loops to build the join table require the following code:

for vOuterLoop = 0 to vMaxDays
     for vInnerLoop = vOuterLoop to vMaxDays
          AccumulatedDays:
          LOAD
               $(vOuterLoop) as [Repayment Day],
               $(vInnerLoop) as [Repayment Day Accumulated]
          AUTOGENERATE(1);
     next
next

And then a small bit of tidying up behind ourselves:

DROP TABLE Temp_MaxDays;

let vMaxDays =;
let vOuterLoop =;
let vInnerLoop =;

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

Accumulated In QlikView Data Model

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:

for vMAT = 0 to 11
     MAT:
     LOAD
          [Month Year],
          Date(AddMonths([Month Year], $(vMAT)),'$(vMonthFormat)') as [MAT Period]
     RESIDENT Repayments
     WHERE AddMonths([Month Year], $(vMAT)) > today();
next

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:

Average Bar In QlikView

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:

DebtorWithAvg:
LOAD
     DebtorID,
     DebtorID as [DebtorID With Avg]
RESIDENT Repayments;

DebtorWithAvg:
LOAD
     DebtorID,
     'Avg' as [DebtorID With Avg]
RESIDENT Repayments;

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.

The QlikView document used to illustrate this blog post can be downloaded from here: Qlik Community Accumulate Values in the Data Model. If you find the document useful please take the time to rate it positively on the community.

By |2019-03-19T12:52:14+00:00April 21st, 2014|QlikView Tutorial|18 Comments

About the Author:

Steve is owner and principal consultant at Quick Intelligence. He is a Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books.

18 Comments

  1. Patrick Tehubijuluw April 21, 2014 at 7:32 pm - Reply

    This again is a great contribution Steve! Thanks.

    • Steve Dark April 21, 2014 at 10:28 pm - Reply

      Thanks Patrick – always good to know that the approaches I use day to day fit with best practice from Qlik.

  2. Gysbert April 22, 2014 at 7:53 am - Reply

    To add to the fun allow me to point out an article I wrote a while ago and that I think goes well with the one above: http://community.qlik.com/docs/DOC-4252.

    • Steve Dark April 22, 2014 at 8:35 am - Reply

      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.

  3. rustyfishbones April 22, 2014 at 11:42 am - Reply

    Hi Steve,

    Great post and thanks for the mention!!

    Regards

    Alan

  4. Manish Kachhia April 22, 2014 at 4:13 pm - Reply

    Great Article as always Steve.

  5. Gonzalo Bianchi April 25, 2014 at 1:10 am - Reply

    HI.. While your solution is great, I have a great deal about how you build the mapping table.

    Even with a low quantity of max days (100) your solution (for..next nested) make the table in about 5 seconds.

    Just try this on your script:

    TRACE test1;

    for i = 0 to 100

    t:
    LOAD
    $(i) as value
    AutoGenerate 1;

    NEXT

    t1:
    LOAD
    value as repdayaccu,
    IterNo()-1 as repday
    Resident
    t
    While
    value >= IterNo()-1;

    DROP Table t;

    TRACE test2;

    for vOuterLoop = 0 to 100
    for vInnerLoop = vOuterLoop to 100
    AccumulatedDays:
    LOAD
    $(vOuterLoop) as [Repayment Day],
    $(vInnerLoop) as [Repayment Day Accumulated]
    AUTOGENERATE(1);
    next
    next

    You will see that the second test take about 5 seconds, while the first one only take 1. If you have a couple of years (days) the time it will take will make it very slow.

    Just 2 cents to make it work really fast ;)

    • Steve Dark April 25, 2014 at 6:41 am - Reply

      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.

      All good fun! Many thanks for the thoughts.

  6. Steve Dark May 31, 2014 at 6:57 am - Reply

    I’ve been pointed to an interesting thread on the community regarding prior periods, that uses a similar technique: http://community.qlik.com/thread/116959

    Well worth a look.

  7. Nick Mustacich December 18, 2014 at 11:08 pm - Reply

    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

    • Steve Dark December 19, 2014 at 1:26 pm - Reply

      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:

      sum({}Value) / sum({}Value)

      That should give you the result that you are after.

      Hope that helps.
      Steve

  8. Nick Mustacich December 19, 2014 at 2:49 pm - Reply

    Steve thank you for insight… that should work. Happy Holidays – Nick

  9. Chris Wain January 15, 2015 at 12:57 pm - Reply

    Hi Steve,

    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]

    FROM
    $(vDataFolder)Balances.QVD
    (qvd);
    next vMAT

    //**** Map the unique key and the MAT total
    MATMap:
    Mapping load
    [Link Field],
    sum([MAT Actual]) as [MAT Actual]
    Resident MAT
    Group by [Link Field];

    Balances:
    //***** Reload the balances table and map on the MAT field
    Load *,
    ApplyMap(‘MATMap’,[Link Field],0) as [MAT Actual];
    LOAD *,
    [Account Code Combination]&’!’&[Period ID]&’!’&[Balance Type] as [Link Field]
    FROM
    $(vDataFolder)Balances.QVD
    (qvd);

    • Steve Dark January 15, 2015 at 11:32 pm - Reply

      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.

  10. Chris Wain January 16, 2015 at 9:28 am - Reply

    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

  11. Enmanuel May 1, 2017 at 2:36 pm - Reply

    Nice post Steve. I used your approach to create a cumulative field by x days. Here’s the link (in spanish):
    http://qlikviewapuntes.blogspot.com.ar/2017/05/acumulados-por-dias.html

  12. Chris Chitemerere October 30, 2017 at 3:40 pm - Reply

    Hi Steve

    Thank you for the insights and calculation of a MAT. In the same vein, how can one calculate the prior year MAT?

    • Steve Dark October 31, 2017 at 1:51 pm - Reply

      It would just be a case of creating entries in the table that link one month to the same month in the prior year (using AddMonths(Date, -12)). Good luck getting it working.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.