6 Replies Latest reply: Oct 24, 2018 7:55 AM by Sunny Talwar RSS

    Calculating Cumulative Percentage of Total by Column (Pivot Table)

    Elin Tham

      Dear Qlik Sense Experts,

       

      steve1982 and I are trying to figure out how to calculate a cumulative percentage of the Total by Column in a Pivot Table in Qlik Sense.

       

      We have a pivot table in Qlik Sense that shows the cumulative count of flags by week for the dimension. We would like to calculate the % of each flag that compares the cumulative count of flags by week over the total cumulative count of all flags of that week.

       

      We're using a unique key = ID to calculate the cumulative count of flags (Flag) by week with certain criteria filters. Expression as below:

      RangeSum(Before(Count({[Criteria]}>}[ID]),0,ColumnNo()))

       

      Capture.PNG

      The below is roughly what we're trying to achieve. The cumulative count of each flag should be divided by the Total by Column as of each week but we're having a hard time trying to come up with an expression that does that.

       

      Flag201827%201828%
      TOTALS12,595100%23,926100%
      ZZ-DO NOT USE1751.39%3241.35%
      ZZ-NEW CUSTOMER AEC NOT VALIDATED890.71%1720.72%
      ZZ-VALIDATED AEC12,33197.9%23,43097.93%

       

       

      Thank you.