Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristofSchwarz
Partner Ambassador
Partner Ambassador

Qlik Sense Accumulating Bar Charts, Waterfall Chart - How To

Accumulating Chart (1 dimension, 1 measure)

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:

Screenshot 2016-09-28 13.34.38.png

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

Screenshot 2016-09-28 13.41.13.png




Accumulating Chart (2 dimensions, 1 measure)

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

Screenshot 2016-09-28 17.57.47.png

Also, this is possible, the formula is more complex to explain.

Screenshot 2016-09-28 17.48.58.png

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


Waterfall Chart (1 dimension, 1 measure)


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.

Screenshot 2016-09-28 16.03.58.png

  1. Put your chart expression into a variable e.g. "vExpr1"
  2. Create a bar chart with two dimensions
    • your main dimension (in above example it is YearMonth)
    • a caluclated pseudo dimension with this formula: =ValueList('', ' ', ' ') Label this dimension with a space (=invisible label)
  3. Add the following expression, where $(Expr1) is your chart expression:
    • Pick(RowNo()
      , RangeMax(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),0,RowNo(TOTAL))) - RangeMax(0, $(vExpr1)))
      + RangeMin(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),0,RowNo(TOTAL))) - RangeMin(0, $(vExpr1)))
      ,Fabs(RangeMax(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),0,RowNo(TOTAL))))
      - RangeMax(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),3,RowNo(TOTAL)))))
      ,-Fabs(RangeMin(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),0,RowNo(TOTAL))))
      - RangeMin(0, RangeSum(Above(TOTAL If(RowNo()=1,$(vExpr1)),3,RowNo(TOTAL)))))
      )
  4. Add this formula in the color by expression formula:
    • If(RowNo()=1, ARGB(0,0,0,0), RGB(68,119,170))

The RGB color code is the Dark Blue of the Qlik Sense standard theme, feel free to change it.


9 Replies
avastani
Partner - Creator III
Partner - Creator III

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?

ylegrand
Contributor II
Contributor II

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:

2017-06-27 17_20_26-Feuille de temps - Interlog Solutions - Projects.png

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.

ChristofSchwarz
Partner Ambassador
Partner Ambassador
Author

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

ylegrand
Contributor II
Contributor II

Thanks for your quick answer.

Unfortunately, it does not work either: it seems that the expression is not evaluated at all (cf screenshot).

2017-06-28 14_27_22-Qlik Sense Desktop.png

2017-06-28 14_27_44-Qlik Sense Desktop.png2017-06-28 14_32_16-Qlik Sense Desktop.png

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?

ylegrand
Contributor II
Contributor II

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.

ChristofSchwarz
Partner Ambassador
Partner Ambassador
Author

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?

ylegrand
Contributor II
Contributor II

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 :

  • The cumul value is about 1000 times the correct value
  • It does not correct the problematic behaviour

Correct values (with DISTINCT) :

2017-07-27 11_56_27-Qlik Sense Desktop.png

Values without DISTINCT

2017-07-27 11_55_35-Qlik Sense Desktop.png

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 ?

ylegrand
Contributor II
Contributor II

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

Anonymous
Not applicable

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