topic Re: Percentage running total - pivot table in App Development

Percentage running total - pivot table

Zara — Mon, 07 Nov 2022 15:22:21 GMT

As a business we measure fall out at certain stages of our process.

In Excel we use percentage of running total in order to achieve this.

I have been able to replicate in Qlik using the below formula which works perfectly, however i would like to change the pivot table round, so that it looks like the excel version.

FORMULA : rangeSum(above(count(Application_ID),0,RowNo()))/
Count(total<[Application Date (Application).autoCalendar.YearMonth]>Application_ID)

I've tried using left rather than above and changing the dimensions but it isn't working. Any ideas?

Re: Percentage running total - pivot table

brunobertels — Mon, 07 Nov 2022 16:20:50 GMT

You may try with before rather than left as far as i remember to have done it in a former app

Re: Percentage running total - pivot table

Zara — Mon, 07 Nov 2022 17:37:59 GMT

You little legend!!! I used before and changed RowNo to ColumnNo and it worked perfectly. Thank you

Re: Percentage running total - pivot table

brunobertels — Tue, 08 Nov 2022 08:38:00 GMT

oups yes sorry my bad i forgot to talk about columnno in replacement of rowno

just in case here below a generic formula to color cells in cross table

ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,red(),green(), yellow())

Re: Percentage running total - pivot table

Zara — Tue, 08 Nov 2022 09:10:15 GMT

Thanks - id already used the colormix before so i was ahead of the game with that one.

Thanks again, its been a problem that's been bugging me for ages. Just kept focusing on left and not before. You have been very helpful.