8 Replies Latest reply: Aug 7, 2017 3:29 AM by yohann legrand RSS

    Qlik Sense Accumulating Bar Charts, Waterfall Chart - How To

    Christof Schwarz

      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.