Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
While I suspect, that in later versions of Qlik Sense there should be more elegant way with a simple click to set charts to accumulate a sequence of bars (the new value totalling up to the previous values), here is an attached app that allows you all of that, illustrated and explained:
To do this you should embrace the so-called intra-chart functions. You need "Above()" and "RowNo()" in this case.
Above with only one argument delivers the result of the "above" dimension, but with a 2nd and 3rd argument will deliver an array of values, so you need another function around to sum this array into one value: RangeSum() ... not Sum() !
RangeSum(Above( sum(ExpenseActual),0,RowNo())) // Where Sum(ExpenseActual) stands for your measure formula
This table shows what's happening in the different parts of the formula
This case is a bit more complex. Two dimensions should accumulate, each of which separately - of course. So the cumulation is calculated for each member of the 2nd dimension separately. (Visually this only makes sense with grouped bars, not with stacked bars).
Also, this is possible, the formula is more complex to explain.
What you can see is that Customers of Type G3 had less accumulated Expenses than G2, which is impossible to see from the above bar chart.
The chart has two dimensions, one measure - here Sum(ExpenseActual), but the measure-formula has to be extended like this
$(=Concat(DISTINCT 'IF([$(=GetObjectField(1))] = ' & CHR(39) & [$(=GetObjectField(1))] & CHR(39)
& ', RangeSum(Above(TOTAL
sum(ExpenseActual)
*-([$(=GetObjectField(1))] = ' & CHR(39) & [$(=GetObjectField(1))] & CHR(39) & '), 0, RowNo(TOTAL)))'
, ',') & Repeat(')', Count(DISTINCT [$(=GetObjectField(1))])))
It gets much more difficult to draw this has to be a waterfall chart. There is no real "bar offset" to the axis, so in reality there has to be a bar segment for the offset with the color set to transparent.
This is doable, but the formula is really complex to explain.
The RGB color code is the Dark Blue of the Qlik Sense standard theme, feel free to change it.
Hi Christof,
the instructions work great. However for the 2 dimension cumulative chart, I am unable to show to visual unless values are selected in the second dimension. Was that a requirement for the cumulative chart to work?
Dear Christof,
Thanks for the formula about the accumulating chart with 2 dimensions.
I experience an issue that I cannot fix by myself after several hours of search: when one of the calculated value is null, the chart does not display it at all:
For example for the last month (June), I should keep the "FAP Auto" with the same value as for May.
Is there a way to do it ?
Thanks a lot in advance for your answer.
Maybe try whenever your nullable expression is Xxx(xxx) ... Or a variable $(xxx) use Alt( Xxx(xxx) ,0) or Alt( $(xxx) ,0) instead
Von meinem iPhone gesendet
Am 27.06.2017 um 17:26 schrieb yohann legrand <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>>:
Thanks for your quick answer.
Unfortunately, it does not work either: it seems that the expression is not evaluated at all (cf screenshot).
Whatever the kind of function I use on this variable output (IF / LEN / ALT / etc.), it returns a null when there is no value at all from the input source.
I have the strong feeling that the change has to be done directly within the below expression, but I can't manage to figure out how:
$(=Concat(DISTINCT 'IF([$(=GetObjectField(1))] = ' & CHR(39) & [$(=GetObjectField(1))] & CHR(39)
& ', RangeSum(Above(TOTAL
count(DISTINCT idFlow)
*-([$(=GetObjectField(1))] = ' & CHR(39) & [$(=GetObjectField(1))] & CHR(39) & '), 0, RowNo(TOTAL)))'
, ',') & Repeat(')', Count(DISTINCT [$(=GetObjectField(1))])))
Does it make sense to you?
Dear Christof,
I am sorry to have to ask you again without extra elements, but do you at least have a clue about a possible adaptation of your formula?
After many tries, I still am not able to adapt it to have the right behavior.
Thanks a lot.
Hi yohan. What is behind your variable $(total_cum) ... looking at your screenshot it seems that you are using maybe an Aggr() function in this formula. If so, try Aggr(NODISTINCT ....) instead .... this will start to fill up every row. If it is not Aggr but a TOTAL argument, the error is somewhere else. Can you share the entire app?
Hi Christof,
$(total_cumul) contains your formula as it, just by replacing sum(ExpenseActual) by count(DISTINCT idFlow)
By removing the DISTINCT, it returns totally wrong result :
Correct values (with DISTINCT) :
Values without DISTINCT
Please find the link to download the full app. The issue is in the bar graph at the bottom of the "Création de flux" sheet : Interlog Sharing
I never tried to share an app. Is it enough or do you need extra files ?
Looks like my file expired after 7 days. I moved my file to Dropbox to keep it until you have a look: Dropbox - Apps.zip
Hi Christof Schwarz,
your post is realy helpful, although i am trying to get the last row as total in the waterfall, thus the offset to be 0.
is there a condition possible where we keep the rangesum formula for all except last row.
Regards
Smrati