topic Re: Accumulation Option disabled in Pivot Table in QlikView

Accumulation Option disabled in Pivot Table

Ahmed_Turnaround — Wed, 26 Feb 2020 10:50:13 GMT

Hi,

Can anyone please explain why Accumulation Option is disabled in a Pivot Table?

Can anyone please assist in getting accumulation working? I need to create a running total for profit (loss) within a month in a P&L sheet.

The calculation is as follows to work it out for the current month:

=(((sum({<[SUB CATEGORY ID] = {'10'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'20'}>} [GL MOVEMENT BASE]) *-1))
- (sum({<[SUB CATEGORY ID] = {'30'}>} [GL MOVEMENT BASE]) *-1))
+ ((sum({<[SUB CATEGORY ID] = {'40'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'50'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'60'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'70'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'80'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'90'}>} [GL MOVEMENT BASE]) *-1))

Let's say this total is 2000.

The next month, should be the following calculation:

Let's say this total is 3000

+ the calculation of the previous month (2000), so the total that should show in the table would be 5000.

Make sense?

Thank you in advance for any help.

Re: Accumulation Option disabled in Pivot Table

Ahmed_Turnaround — Wed, 26 Feb 2020 14:50:53 GMT

Just an update, I've managed to get the totals to move across, using this code here that I found on the forum. I've put this into an expression within my pivot table:

=RangeSum(Alt(Before((((sum({<[SUB CATEGORY ID] = {'10'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'20'}>} [GL MOVEMENT BASE]) *-1))
- (sum({<[SUB CATEGORY ID] = {'30'}>} [GL MOVEMENT BASE]) *-1))
+ ((sum({<[SUB CATEGORY ID] = {'40'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'50'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'60'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'70'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'80'}>} [GL MOVEMENT BASE]) *-1)
+ (sum({<[SUB CATEGORY ID] = {'90'}>} [GL MOVEMENT BASE]) *-1))),0,))

So, my output now looks like so:

However, as you can see, it's showing 0 for April where I would have expected to see 1000.

For the following months, it hasn't added on the previous month, it's just taken the previous month value. I.e. the value of 1000 for April has been put into May where I would have expected to see 4000.

Any ideas?

I've had to change the figures and hide the data due to confidentiality and sensitivity of data.

Re: Accumulation Option disabled in Pivot Table

Brett_Bleess — Tue, 31 Mar 2020 17:54:39 GMT

Check the following Design Blog post to see if that helps you get what you need:

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

Regards,
Brett