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.
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.
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.
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.
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.
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())
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.