Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I need to calculate the sum of values of last two months in script.Here i am attaching the code and the sample application for reference.
Please go through it and help me.
for eg:
Tab:
LOAD * Inline [
Data , Month
2, 1
2,1
4, 2
5, 2
1, 3
2,4 ];
i have the above sample data and i need to sum of two previous months say if i select month 2 then it should show the sum of previous two months say 1 ,2 and sum should be 13 in total for this selection.
I want to achieve the above result by script only not on frontend.
Regards,
Vasudha
May be something like this:
Tab:
LOAD * Inline [
Data , Month
2, 1
2, 1
4, 2
5, 2
1, 3
2, 4
];
Table:
LOAD Sum(Data) as Total,
Month
Resident Tab
Group By Month;
Join(Tab)
LOAD If(Month = Previous(Month) + 1, RangeSum(Total, Previous(Total))) as CumulativeTotal,
Month
Resident Table;
DROP Table Table;
Output:
May be something like this:
Tab:
LOAD * Inline [
Data , Month
2, 1
2, 1
4, 2
5, 2
1, 3
2, 4
];
Table:
LOAD Sum(Data) as Total,
Month
Resident Tab
Group By Month;
Join(Tab)
LOAD If(Month = Previous(Month) + 1, RangeSum(Total, Previous(Total))) as CumulativeTotal,
Month
Resident Table;
DROP Table Table;
Output:
Thanks sunindia
Hi Sunny ,
I want same like this , but for me i want 3 fields,
1 should give 1 month previous sum
2nd should give 2 month previous sum
3rd should give 3 month previous sum
and i have year field also so when january month came then it should sum with last year december also.
can it possible in script ?
Hi,
Yes it is possible by using MasterCalendar with Rolling months.
And You can do separate calculation for all by using rolling 2,3,...etc. and do the join based on Key.
For Example,
/Current Month
AsOfMonth:
Load
'Current' as RollingType,
Period as AsOfPeriod,
Period as Period
Resident Calendar;
//Rolling 3 month
AsOfMonth:
Load
'R3M' as RollingType,
Period as AsOfPeriod,
AddMonths(Period,1 - IterNo()) as Period
Resident Calendar
while IterNo() -1 < 3;
And you can use doing join with facttable and groupby. If you want separate column then each time you need to load specific rolling type and follow the process.
Thanks
Ravi