topic Re: running total in pivot table of Adhoc report in QlikView

running total in pivot table of Adhoc report

shashank181993 — Fri, 28 Aug 2020 12:44:12 GMT

Hi Team,

I want to make a measure to calculate the running total in an ad-hoc report where the dimensions gets added from the list box on the left. The last column is not making the cumulative sum as the new dimensions gets added from the list box any ideas?

=RangeSum(Above( sum({<CalendarDateNum={'<=$(vPerfEndDate)'}> } Amount),0,rowno(TOTAL)))

Re: running total in pivot table of Adhoc report

sunny_talwar — Fri, 28 Aug 2020 12:53:10 GMT

May be try this

=RangeSum(Above(TOTAL Sum({<CalendarDateNum = {'<=$(vPerfEndDate)'}>} Amount), 0, RowNo(TOTAL)))

The above will give you a accumulation across all dimensions.... is that what you want?

Re: running total in pivot table of Adhoc report

shashank181993 — Fri, 28 Aug 2020 13:00:06 GMT

Thanks, sunny! I tried this but the subtotal accumulate the data from different GL type which need to be restricted

Re: running total in pivot table of Adhoc report

shashank181993 — Fri, 28 Aug 2020 13:30:17 GMT

Hi @sunny_talwar,

I have attached the sample data here need in the pivot table to calculate the rangesum

Re: running total in pivot table of Adhoc report

PrashantSangle — Fri, 28 Aug 2020 13:44:30 GMT

you can use combination of secondarydimensionality() with Sunny's solution.

Re: running total in pivot table of Adhoc report

sunny_talwar — Fri, 28 Aug 2020 13:51:16 GMT

Somehow I am unable to download the attachment because the Virus scan is in progress

Re: running total in pivot table of Adhoc report

Brett_Bleess — Wed, 09 Sep 2020 14:37:36 GMT

@sunny_talwar I checked the attachment, I can get it, so not sure if we had an issue that got fixed or what, if you get a chance, try again and shout if you still get the virus scan message, but try downloading, that is what I did versus clicking to open the file, sorry, I think you may have opened. Preview did work for me too though, so not sure what the heck happened to you.

Cheers,
Brett

Re: running total in pivot table of Adhoc report

sunny_talwar — Wed, 09 Sep 2020 14:43:37 GMT

Thanks @Brett_Bleess, but this is not the kind of sample I was expecting. This is pre-aggregated data in a chart, I was looking for more like a raw sample data.

Re: running total in pivot table of Adhoc report

Brett_Bleess — Wed, 09 Sep 2020 15:07:43 GMT

@shashank181993 Please see Sunny's latest comment above and respond accordingly, if you figured things out, please post what you did and then mark that as the solution using the Accept as Solution button.

Regards,
Brett