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

# Calculating Cumulative Percentage of Total by Column (Pivot Table)

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

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.

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

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

Sum(TOTAL <WeekYearField> Aggr(RangeSum(Above(Count({<[Criteria]>} [ID]), 0, RowNo())), Flag, (WeekYearField, (NUMERIC))))

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

Thank you very much, Sunny!

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

Hi Sunny,

Sorry, but I have a few more questions.

What if I have another dimension "Flag Corrected" and I only want to show those Flag Corrected = Y in the Pivot Table? Is it possible to do so in the pivot table? I've tried to do Qlik Expression under row: =if(Flag Corrected = {"Y"}, Flag Corrected) but it doesn't seem to work.

I also would like to calculate the cumulative percentage of Flag Corrected = Y over the total cumulative count of ZZ-VALIDATED AEC flag. For example in Week 201827, the % for Flag Correct = Y should be 55/12,331 = 0.446% instead of 55/12,595=0.436%. Do you think it is possible to do so in a pivot table?

I've tried to create another pivot table to only show the Flag Corrected but am unable to get the cumulative percentage of Flag Corrected = Y over the total cumulative count of ZZ-VALIDATED AEC flag.

I'm using this expression:

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

Sum(TOTAL <WEEK> Aggr(RangeSum(Before(Count({<[Criteria with Flag = ZZ-VALIDATED AEC]>}ID),

0, ColumnNo())), Flag,(WEEK, (NumericCount))))

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

May be this for showing just Flag Corrected = 'Y'

RangeSum(Before(Count({<[Criteria], [Flag Corrected] = {'Y'}>}[ID]), 0, ColumnNo()))/

Sum(TOTAL <WeekYearField> Aggr(RangeSum(Above(Count({<[Criteria], [Flag Corrected] = {'Y'}>} [ID]), 0, RowNo())), Flag, (WeekYearField, (NUMERIC))))

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

I also would like to calculate the cumulative percentage of Flag Corrected = Y over the total cumulative count of ZZ-VALIDATED AEC flag. For example in Week 201827, the % for Flag Correct = Y should be 55/12,331 = 0.446% instead of 55/12,595=0.436%. Do you think it is possible to do so in a pivot table?

May be this

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

Sum(TOTAL <WeekYearField, Flag> Aggr(RangeSum(Above(Count({<[Criteria]>} [ID]), 0, RowNo())), Flag, [Flag Corrected],(WeekYearField, (NUMERIC))))

• ###### Re: Calculating Cumulative Percentage of Total by Column (Pivot Table)

Is there a third question?