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.
Note that whilst this post was written prior to the release of Qlik Sense the technique of aggregating in the load script is just as valid. There is a slight syntax change when referencing files, but apart from that the source code will work.
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.
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:
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:
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 DISTINCT
DebtorID,
DebtorID as [DebtorID With Avg]
RESIDENT Repayments;
DebtorWithAvg:
LOAD DISTINCT
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.
This again is a great contribution Steve! Thanks.
Thanks Patrick – always good to know that the approaches I use day to day fit with best practice from Qlik.
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: https://community.qlik.com/t5/Member-Articles/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033
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 Steve,
Great post and thanks for the mention!!
Regards
Alan
Great Article as always Steve.
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 ;)
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.
I’ve been pointed to an interesting thread on the community regarding prior periods, that uses a similar technique: https://community.qlik.com/t5/QlikView-App-Dev/How-to-trend-Current-Sales-vs-Prev-Sales-using-date-as-dimension/td-p/587947
Well worth a look.
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:
sum({}Value) / sum({}Value)
That should give you the result that you are after.
Hope that helps.
Steve
Steve thank you for insight… that should work. Happy Holidays – Nick
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);
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
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
Hi Steve
Thank you for the insights and calculation of a MAT. In the same vein, how can one calculate the prior year MAT?
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.