topic Re: Can change accumulation options in Pivot Table? in QlikView

Can change accumulation options in Pivot Table?

jim_chan — Wed, 03 Aug 2016 03:27:16 GMT

Hi guys,

I know there's a accumulation function/options to select in Straight Table.

But when i switch to Pivot, i cant make any change on the accumulation option, cant select any.

Rgds,

Jim

Re: Can change accumulation options in Pivot Table?

arulsettu — Wed, 03 Aug 2016 03:50:52 GMT

you can accumulate by using expression

for example

rangesum(above(sum(sales),0,rowno()))

Re: Can change accumulation options in Pivot Table?

Siva_Sankar — Wed, 03 Aug 2016 03:51:32 GMT

Jim,

Accumulation option will not be available in pivot table. Workaround is you have to user rangesum or other range functions. You may use above or below functions also.

Refer the following threads also

Accumulation in a pivot table | Qlik Community

Sum | Qlik Community

Regards,

Siva

Re: Can change accumulation options in Pivot Table?

sunny_talwar — Wed, 03 Aug 2016 09:16:01 GMT

Are you still accumulating vertically or is the accumulation done horizontally now?

For horizontal accumulation, may be this:

RangeSum(Before(Sum(Value), 0, ColumnNo())) or

RangeSum(Before(TOTAL Sum(Value), 0, ColumnNo(TOTAL)))

For vertical accumulation, may be this

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

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

Re: Can change accumulation options in Pivot Table?

jim_chan — Wed, 03 Aug 2016 09:23:14 GMT

Hello Sunny, i will try it out 1st with your solution.

Thanks,.

Re: Can change accumulation options in Pivot Table?

jim_chan — Wed, 03 Aug 2016 10:29:10 GMT

Hey bro,

i have tried with the RangeSum(Before(Sum(Value), 0, ColumnNo())).

But looks weird on my pivot table. Or Do you have a sample qvw file that have applied with this expression so i can refer to ?

Rgds,

Jim

Re: Can change accumulation options in Pivot Table?

sunny_talwar — Wed, 03 Aug 2016 10:31:27 GMT

Would it be possible to share an image of what you are seeing. I want to see the weirdness you are seeing

Re: Can change accumulation options in Pivot Table?

Fri, 05 Aug 2016 10:12:36 GMT

Hi Sunny,

I am working with Jim on this issue.

This is the result that we get. the expression is look fine for a month, when trying to calculate for two month. the amount is incorrect for 2nd month.

the expression we are using is = RangeSum(Before([Standard Cost], 0, ColumnNo(TOTAL)))

Re: Can change accumulation options in Pivot Table?

sunny_talwar — Fri, 05 Aug 2016 11:22:16 GMT

How about if you do this:

RangeSum(Before(TOTAL [Standard Cost], 0, ColumnNo(TOTAL)))