Cumulative sum with two dimension.

[#1]

Hi everybody, Could you Please help me in finding solution for this problem. I am attaching the data sheet with the problem.

I have a date field and an Amount field.

Date	Amount
01-Apr-12	100
01-May-12	200
01-Jun-12	300
01-Jul-12	400
01-Aug-12	0
01-Sep-12	100
01-Oct-12	0
01-Nov-12	120
01-Dec-12	100
01-Jan-13	200
02-Jan-13	1500
01-Mar-13	200
01-Apr-13	100
01-May-13	100
01-Jun-13	100

I have  to show the data in a straight table as cumulative sum of Amount.

the Conditions are:

Here the cumulative sum of 3rd row will be sum of 3rd 2nd and 1st Amount.
The year starts at April and ends at March so when April 2013 comes, the sum should start again.
The value 0 should be displayed in the chart.

Date	Amount	Cumulative
01-Apr-12	100	100
01-May-12	200	300
01-Jun-12	300	600
01-Jul-12	400	1000
01-Aug-12	0	1000
01-Sep-12	100	1100
01-Oct-12	0	1100
01-Nov-12	120	1220
01-Dec-12	100	1320
01-Jan-13	200	1520
02-Jan-13	200	1720
01-Mar-13	200	1920
01-Apr-13	100	100
01-May-13	100	200
01-Jun-13	100	300

Can it be done. If I remove the Amount field. I can easily get the cumulative sum. But adding Amount dimension creates a problem.

[mrybalko]

I suggest to calculate cumulative sum by loading script.

Two tips:

Calculate finance year

year(AddMonths(Date, -3)) as FinanceYear

Then use functions previous and peek to calculate cumulative sum

if(FinanceYear=Previous(FinanceYear),

  peek('CumulativeAmount') + Amount,

  Amount) as CumulativeAmount

Example in attachment.

[#3]

Thanks Maxim

Thanks a lot.

[#4 · Accepted solution]

I got the solution for it. If we take year as a seperate field in load statement like shown below.

LOAD Date,

Year(Date),

if(Month(Date)='Jan',Year(Date)-1,if(Month(Date)='Feb',Year(Date)-1,if(Month(Date)='Mar',Year(Date)-1,Year(Date)))) as FY,

Amount

FROM

CUMULATIVE.xlsx.

Now in a chart i.e straight table take Date as dimension, FY as dimension, Amount as Expression and Sum(Amount)  as Expression.

Make sure the Accmulation is checked and Hide Fy . You can get the desired result set.

[#5]

Just to clean that up a bit:

LOAD

Date,

Year(Date)

if(Num(Month(Date))<=3,Year(Date)-1,Year(Date) As FY,

etc.