topic Re: Accumulate Partial Sum on Pivot Table in QlikView

Accumulate Partial Sum on Pivot Table

pljsoftware — Wed, 01 Feb 2017 00:41:29 GMT

Hi all,

I have a pivot table and I try to obtain a progressive value on Total rows as explained in the image.

I attached a *.qvw

Those are a simple data values.

LOAD * INLINE [

Year, Month, Group, Value

2017, 1, A, 1

2017, 1, B, 2

2017, 1, C, 3

2017, 1, D, 4

2017, 2, A, 5

2017, 2, B, 6

2017, 2, C, 7

2017, 2, D, 8

2017, 3, A, 9

2017, 3, B, 10

2017, 3, C, 11

2017, 3, D, 12

2017, 4, A, 13

2017, 4, B, 14

2017, 4, C, 15

2017, 4, D, 16

];

I hope someone can help me.

Best Regards

Luca Jonathan Panetta

Re: Accumulate Partial Sum on Pivot Table

sunny_talwar — Wed, 01 Feb 2017 01:32:39 GMT

Try this:

Pick(Dimensionality(),

Sum(Value),

Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month),

RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))))

Re: Accumulate Partial Sum on Pivot Table

pljsoftware — Wed, 01 Feb 2017 02:23:20 GMT

Hi Sunny,

I want to have the value in column 1 for A, B, C, D and the cumulative value for Total.

The correct values will be

2017	1	A	1
		B	2
		C	3
		D	4
		Total	10
	2	A	5
		B	6
		C	7
		D	8
		Total	36
	3	A	9
		B	10
		C	11
		D	12
		Total	78
	4	A	13
		B	14
		C	15
		D	16
		Total	136
	Total		136

Regards

Luca Jonathan Panetta

Re: Accumulate Partial Sum on Pivot Table

sunny_talwar — Wed, 01 Feb 2017 02:26:23 GMT

This:

If(Dimensionality() <> 2, Sum(Value),

Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month))

Re: Accumulate Partial Sum on Pivot Table

pljsoftware — Wed, 01 Feb 2017 02:29:04 GMT

I found solution.

Pick(Dimensionality(),

Sum(Value),

Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month),

Sum(Value)

)

Thanks Sunny for your help. I have change only the 3th expression from RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))) to Sum(Value)

Best regards.

Luca Jonathan Panetta

Re: Accumulate Partial Sum on Pivot Table

pljsoftware — Wed, 01 Feb 2017 02:33:51 GMT

Thanks Sunny.

I mark your answer as correct because your idea helped me to solve the problem.

But I like more Pick function than not IF.

Best regards

Luca Jonathan Panetta

Re: Accumulate Partial Sum on Pivot Table

sunny_talwar — Wed, 01 Feb 2017 02:36:05 GMT

If you like Pick more, you can always mark your own response as correct. You can mark my responses as Helpful if you want (you don't have to)

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

topic Re: Accumulate Partial Sum on Pivot Table in QlikView

Accumulate Partial Sum on Pivot Table

Re: Accumulate Partial Sum on Pivot Table

Re: Accumulate Partial Sum on Pivot Table

Total

10

Total

36

Total

78

Total

136

Total

136

Re: Accumulate Partial Sum on Pivot Table

Re: Accumulate Partial Sum on Pivot Table

Re: Accumulate Partial Sum on Pivot Table

Re: Accumulate Partial Sum on Pivot Table