6 Replies Latest reply: Aug 7, 2015 2:26 PM by John Lee

# Accumulative sum for two dimensions

Greetings!

In this example we're trying to accumulate market values, 'MV', by 'Period' (January to December, or last month of the year) and 'Region', (A, B, C, or D), and then graph the results in a line chart.

Using RangeSum(Above(Sum(MV),0,Month)) produces the correct results when the pivot table is collapsed to the 'Period' dimension, but once you expand the pivot table to 'Region' the formula breaks.

We can mimic the correct results by dragging the 'Region' dimension across the top and using the previousRangeSum() formula. But once you convert this pivot table to a line chart the accumulation is lost.

tl;dr

Accumulate market values from January to December (or last month of the year), and break it up by region.

Thanks!

JDM

• ###### Re: Accumulative sum for two dimensions

Like this?

You need to take care that Period is sorted chronological in load order (like in your sample data) to make this solution using advanced aggregation work with your real data.

• ###### Re: Accumulative sum for two dimensions

Hi swuehl!

Thanks for your response. It works in the sample data, but not in my real data.

I've sorted Period and Region in the load script:

Activity_2:

PERIOD,

MONTH,

YEAR,

ACTIVITY,

PERCENTAGE,

COUNTRY,

NAME,

DIVISION,

REGION

Resident Activity_1 Order by PERIOD, REGION;

DROP Table Activity_1;

But when I apply the aggr formula the data breaks again:

Aggr(RangeSum(Above(Sum(ACTIVITY * PERCENTAGE/100),0,MONTH)),REGION,PERIOD)

• ###### Re: Accumulative sum for two dimensions

Unfortunately, you won't change LOAD order of a field if you do something like

T1:

FROM Source;

NOCONCATENATE

RESIDENT T1

ORDER BY Field;

DROP TABLE T1;

When you LOAD Field the second time, the load order of the field will still be determined by the first load (It's not about the order of values in the table, but in the field).

You need to do something like this:

Create a temporary table with Period values ordered chronological first in your script, something like

Tmp:

Autogenerate 36;

Month(Period) as Month,

Year(Period) as Year,

Region,

MV

FROM

[C:\Users\leejo\Desktop\Example.xls]

(biff, embedded labels, table is Sheet1\$);

Then drop the temp table:

DROP TABLE Tmp;

• ###### Re: Accumulative sum for two dimensions

It worked! Thanks so much!

• ###### Re: Accumulative sum for two dimensions

Hope you are looking for something like this..

• ###### Re: Accumulative sum for two dimensions

Thanks for your reply! The sum should cumulate by region and year. So for example in March 2011: Region A should have a value of 28 (22+4+2) , Region B should have 25, and so on