13 Replies Latest reply: Jul 1, 2016 10:55 AM by Sunny Talwar RSS

    Full accumulation

    xia ZHU

      I’m looking for a way to accumulate a measure in a chart object in Qlik Sense. In QlikView I can choose "Full accumulation" on the expressions tab, but it seems there's no such feature in Qlik Sense. I have checked about it in this forum. It seems “Full accumulation” not yet included. I should use rangsum(above ……)

      So I want to know if it’s always true?

        • Re: Full accumulation
          Sunny Talwar

          Yes, in order to do full accumulation on the front end in Qlik Sense, you can only use RangeSum(Above()) or RangeSum(Below()) based on your sorting. Another method can be via using the As-Of Table (The As-Of Table), but this will require intervention in the script and may not be the best way to do full accumulation.

            • Re: Full accumulation
              xia ZHU

              It's shame, it's easier to do full accumulation in Qlikview.

              thanks

                • Re: Full accumulation
                  Sunny Talwar

                  Xia -

                   

                  Although I agree that full accumulation is convenient, but I almost never use it because I think it is very restrictive. One issue that I have with full accumulation is that it is based on your selection. No matter what you do, you can never show accumulation from unselected part. Whereas RangeSum(Above()) gives you the power to control how you want to see the accumulation.

                   

                  Another issue I have seen is that sometimes you want to calculate the cumulative percentage as another expression and here again you cannot use full accumulation and will have to rely on RangeSum(Above()).

                   

                  This is my general view, but feel free to disagree with me

                   

                  Best,

                  Sunny

                    • Re: Full accumulation
                      xia ZHU

                      Hi Sunny,

                       

                      Maybe you are right.

                      I tried o use rangesum(above), i works fine juste with only one dimenche. In the case 2 dimensions, i've used aggr in qlikview, but it don't work in qlik sense.

                        • Re: Full accumulation
                          Sunny Talwar

                          Qlik Sense and QV12 are the best candidates for full accumulation using Aggr function as it gives the ability to sort within the Aggr function which was not previously available in QlikView 11 and before.

                           

                          Do you have a specific example which isn't working for you? May be we can help you with it and in the process you might learn something new

                            • Re: Full accumulation
                              xia ZHU

                              For example,

                               

                              i want to compare sales accumulated for 2015 and 2016.

                              this is the data

                               

                              yearmonthamout
                              2016110
                              2016220
                              2016330
                              2016440
                              2016550
                              2016660
                              20167
                              20168
                              20169
                              201610
                              201611
                              201612
                              2015115
                              2015220
                              2015325
                              2015430
                              2015535
                              2015640
                              2015745
                              2015850
                              2015955
                              20151060
                              20151165
                              20151270

                              i used rangesum(above(sum(    ..

                              i got someting like this in qlik sense

                              2016-06-30_17h47_37.png

                              and i want to get chart like this(in qlikview) with full accumulate

                               

                              2016-06-30_17h50_04.png

                                • Re: Full accumulation
                                  Sunny Talwar

                                  Like this?

                                   

                                  Capture.PNG

                                   

                                  Expression:

                                  If(Sum(amout) > 0, Aggr(RangeSum(Above(TOTAL Sum(amout), 0, RowNo())), year, (month, (NUMERIC))))

                                    • Re: Full accumulation
                                      xia ZHU

                                      Hi sunny

                                       

                                      Your solution work well.  But i find it very complicated. For me qliksense is for user operational not for user IT.

                                      I'm not sure user operational can understand it.

                                       

                                      Moreover, i trid to implante set analysis (par example i want to show just  for the month 4---8)), it don't work in the first time, and i finded than  i should handle the propertises of chart.

                                       

                                      otherwise, do you know if trigger is implanted in qlik sense? 

                                      thanks for your help

                                        • Re: Full accumulation
                                          Sunny Talwar

                                          I guess Qlik Sense is still a relatively new product and they are still enhancing its capabilities. I am sure this is one of those things that would eventually make it to Qlik Sense, but unfortunately, right now we will all have to live with what we have.

                                           

                                          Set analysis can be implemented, but like the RangeSum expression itself, they might make the expression even more messier. Do you still want me to show you how it can be done?

                                           

                                          I don't know if triggers are available in Qlik Sense or not, I work mostly on QlikView and the little I know are the things which are common between QlikView and Qlik Sense.