topic Accumulation in Pivot Table in QlikView

Accumulation in Pivot Table

Saro_2306 — Sat, 16 Nov 2024 17:57:28 GMT

Hi all,

I need to calculate YTD value in the pivot table with 3 dimensions, as below. I cant able to write an expression for this. anybody, please help me. Thanks in advance.

D1	D2	D3	A	B	D	E	(A/B) / (D/E)	YTD
2011	India	Delhi	100	2	50	2	2	2
2011	Pak	Karachi	200	5	100	10	4	4
2012	India	Delhi	300	3	200	4	2	4
2012	Pak	Karachi	400	4	150	3	2	6
2013	India	Delhi	500	2	250	5	5	9
2013	Pak	Karachi	600	3	300	3	2	8

Re: Accumulation in Pivot Table

MayilVahanan — Sun, 18 Oct 2020 01:29:43 GMT

Hi Saro,

Try like below

RangeSum(
Aggr(
RangeSum(Above(Sum([(A/B) / (D/E)]), 0, RowNo())), D3, D2, D1))

Sample Output:

Hope it helps.

Re: Accumulation in Pivot Table

Saro_2306 — Sun, 18 Oct 2020 04:27:03 GMT

Thanks Bro, Will it works for Calculated expression. Bcs in my case all A, B, D, E are calculated expressions.

Re: Accumulation in Pivot Table

MayilVahanan — Mon, 19 Oct 2020 01:28:50 GMT

HI Saro

Yes, it will work.
Replace A,B,D,E with your expressions

Re: Accumulation in Pivot Table

Saro_2306 — Mon, 19 Oct 2020 15:43:33 GMT

No, Its not working all values showing "0"

Re: Accumulation in Pivot Table

MayilVahanan — Tue, 20 Oct 2020 07:22:10 GMT

Hi Saro,

Can you please provide your sample file to proceed further