Cumulative calculation at script level

[#1]

Hi all,

can you please help me in calculation of cumulative values at script lavel for a pivot table.

The data and output file are provided in excel sheet attached.

thanks in advance.

Regards,

GVR

[#2]

Please find the another output format in the attached spread sheet .

[#3]

The cumulative values should not change on selection. It is a fixed value as we are going to add amount by each month

[Miguel_Angel_Baeyens]

Hello Veman,

Using your sample data, might look like

Actual:LOAD Location, Date, Amount, If(Location = Previous(Location), RangeSum(Amount, Peek('Cumulative')), Amount) AS CumulativeRESIDENT TempORDER BY Location, Date;

Hope that helps!

[#5]

Hi,

Thanks for your response. Its working good but I need to add calender from jan 2010 to dec 2010. It is not displaying all the months.

when I join a calender to this the values getting duplicated.

Thnaks&Regards,

Veman

[Miguel_Angel_Baeyens]

Hi Veman,

If you link your table to a master calendar (check here), you don't need to replicate values. You will use your field containing Month as dimension in your chart (it will exist as month) it will display all possible values, showing 0 for those months that don't have amount.

Regards.

[#7]

If you want the cumulative value for every month, you will need to create a table that has every month per location, then left join it with the data to make sure you carry the cumulative values forward.

e.g.

temp:
LOAD * INLINE [
 type,month,val
 a, 1/3/10, 1
 a, 1/5/10, 2
 a, 1/7/10, 3
 b, 1/2/10, 4
 b, 1/4/10, 5
 b, 1/7/10, 6
 c, 1/2/10, 7
 c, 1/6/10, 8
 c, 1/7/10, 9
];


calendar:
load AddMonths('01/01/2010',RecNo()-1,0) as month
autogenerate (24);
temp1:
load distinct type resident temp;
inner join load month,0 as cume resident calendar ;

temp2:
noconcatenate
load RowNo() as sortseq,* resident temp1 order by type,month;
left join load type,month,val resident temp
;
cume:
noconcatenate
load sortseq,type,month,val,if(peek(type)=type,RangeSum(peek(cume),val),val) as cume
 resident temp2
 order by sortseq
 ;

drop tables temp, temp1, temp2;

[georgedevries]

Hi all,

I've tried the solution, but it does not always give the expecting results when using multiple dimensions.

When selecting all dimensions the results are correct, but when not selecting dimension members, the cumulative values are incorrect.

Note: the cumulatation on total levels goes only if all underlying dimensions exist...

See attached screenshots for example.

Regards,

George

[#9]

This might help you..... see the output and attached app

!

[#10]

Hello, would you please send me the code of that file ? Here o by email alexbelfas@gmail.com