topic Re: cumulative sum for pivot in QlikView

cumulative sum for pivot

jduluc12 — Fri, 31 Aug 2018 12:26:36 GMT

Hi,

My dataset looks like this

ID	Name	Item	Amt	Tran	perc
1	custA	ItemA	100	1	.01
1	custA	ItemB	40	1	.07
1	custB	ItemA	30	2	.08
1	CustA	ItemB	20	2	.07
2	CustC	ItemB	30	2	.07
3	CustB	ItemA	30	1	.01
3	CustA	ItemB	20	2	.07
3	CustC	ItemC	20	1	.08

My pivot table should look like this

Dim1: ID

Dim2: Name

Dim3 : Item

Expression 1: sum({<Tran=1>}Amt)

Expression 2: sum({<Tran=2>}Amt)

Expression3: perc

Expression4: (Expression 1 - Expression 2) * perc

The Total of Expression4 has to be a cumulative total.

Id	Name	Item	Expression1	Expression2	Expression3	Expression4
1	custA	ItemA	100	40	.01	0.6
		ItemB	40	20	.07	1.4
	CustB	ItemA	0	20	.08	-1.6
	Total		140	80		0.4

and so on.

I am trying things like

if(RowNo() <>0, (sum({<Tran={1}>}[Amt])

sum({<Tran={2}>}fabs([Amt]))) * only(perc)

(rangesum(above(sum({<Tran={1}>}[Amt]) , 0, NoOfRows()))

rangesum(above(sum({<Tran={1}>}fabs([Amt])), 0, NoOfRows())

)) * only(perc)

)

The row value comes out fine but the Total value is null or missing.

Please help.

Thanks,

Jean

Re: cumulative sum for pivot

sunny_talwar — Fri, 31 Aug 2018 12:31:58 GMT

Did you try this

Sum(Aggr((Sum({<Tran = {1}>} Amt) - Sum({<Tran = {2}>}Amt)) * perc, ID, Name, Item))

Updated the expression to add {} around the set modifier

Re: cumulative sum for pivot

jduluc12 — Fri, 31 Aug 2018 12:39:30 GMT

I tried that but somehow it brings every value as 0.

Re: cumulative sum for pivot

sunny_talwar — Fri, 31 Aug 2018 12:39:57 GMT

Had to modify your data to match the result for 1st three expressions....

LOAD * INLINE [

ID, Name, Item, Amt, Tran, perc

1, CustA, ItemA, 100, 1, .01

1, CustA, ItemA, 40, 2, .01

1, CustA, ItemB, 40, 1, .07

1, CustA, ItemB, 20, 2, .07

1, CustB, ItemA, 20, 2, .08

2, CustC, ItemB, 30, 2, .07

3, CustB, ItemA, 30, 1, .01

3, CustA, ItemB, 20, 2, .07

3, CustC, ItemC, 20, 1, .08

];

But see what I get

Re: cumulative sum for pivot

sunny_talwar — Fri, 31 Aug 2018 12:40:45 GMT

I copy pasted your expression for Expression 1 and Expression 2 which were missing {}... I have modified the expression... give it a shot now

Re: cumulative sum for pivot

jduluc12 — Fri, 31 Aug 2018 12:46:40 GMT

My original dataset is little more complicated than this one but the structure is the same.

It is not working there and bringing 0 values.

I am looking at my data set if i am missing anything,

will update you shortly.

Re: cumulative sum for pivot

sunny_talwar — Fri, 31 Aug 2018 12:50:10 GMT

Sure