Calculate Cumulative Sum

[#1]

I have some data like

Month	Revenue
Jan	100
Feb	200
Mar	150

Now i want to create a bar graph, i use month in dimension and against each month i need cumulative sum of revenues like against jan the value should be 100 , Feb 300 ,Mar 450 and apart from this when i clik on any month then against that month the value of revenue again should be cumulative. Like if a clik on Feb data should be filtered and now i have only one bar against feb and the value of revenue should be 300 not 200.

Can anyone help me on this like what changes needed to be done either at the UI level or at the script level. Please let me know in case you want any more clarity on the same.

Thanks in advance

Regards,

Rahul

[#2]

HI dear

use the following function :

Load Month, Revenue, numsum(Revenue, peek( 'Bsum' )) as Bsum

this will help

[Miguel_Angel_Baeyens]

Hello Rahul,

Following your table, the following expression should work

RangeSum(Above(Sum(Revenue), 0, RowNo()))

Is this what you are looking for?

[#4]

Hi! Miguel/ Rishi

Thanks a lot for replying.

Miguel by ur expression the cumulative sum for all the months is getting calulated but when i select a perticular month then the data is limited to that particular month it does not show the cumulative sum for that month which i need.

Rishi using your expression it is giving me the perfect result with the dummy data which i have given in my post but when i am using it with my real data then the data is not comming correct so do i need to sort my data in some way. I have written the expression you have given in script and then i have used sum(Bsum) in expression.

Please let me know if i am missing something. Thanks again to both of you for replying. I am attaching a dummy application created.

Regards,

Rahul

[johnw]

I'd use an AsOf table to connect each month to all previous months. Assuming this is just to get YTD totals instead of a full accumulation from the beginning of time you could load it like this:

DummyData:
LOAD date(makedate(2010,Month),'MMM YYYY') as Month, Revenue INLINE [
Month, Revenue
1, 100
2, 200
3, 150
];

AsOf:
LOAD
Month as AsOfMonth
,date(addmonths(Month,-iterno()+1)) as Month
RESIDENT DummyData
WHILE iterno() <= month(Month)
;

dimension = AsOfMonth
expression = sum(Revenue)

Edit: I don't recommend above() since as you've already noticed, it messes up when you select a specific month. I don't recommend accumulating in the script because it then won't be sensitive to other selections (not relevant in the example, but might be in the real application).

[#6]

Hi! John,

Thanks a lot, its working prefectly

Regards,

Rahul

[Miguel_Angel_Baeyens]

Hello John,

I'm going to use this since my tests work better this way than my previous RangeSum() + Above() expressions.

I've to admit my stubbornes sometimes.

Thanks and regards.

[johnw]

Miguel Angel Baeyens wrote: I'm going to use this since my tests work better this way than my previous RangeSum() + Above() expressions.
I've to admit my stubbornes sometimes.

*chuckle* Thing is, even though I seem to suggest this twice per day on average, I have NEVER used it in a real application. For some reason, our company just hasn't much needed this sort of accumulation, this sort of date range comparison. So I really don't know how well it works in real, high-volume applications with users making various selections. All I know is that it seems to work great in tiny, sample applications with almost no selections to be made.

And it follows my general philosophy of if it can be handled with the data model, handle it with the data model.

[#9]

Hi John -

Is it possible for you to explain your example above if I have a data not for just this year but from 2001 and want to see cumulative sum for year user selects in the list box.

Thanks for your help.

Amey

[johnw]

By month? So February 2007's accumulation column should show the sum of both February 2007 and January 2007? Something like this (untested).

[AsOf]:
LOAD DISTINCT Month
RESIDENT [Calendar]
;
LEFT JOIN ([AsOf])
LOAD Month as AsOfMonth
RESIDENT [AsOf]
;
INNER JOIN ([AsOf])
LOAD *
RESIDENT [AsOf]
WHERE Month >= yearstart(AsOfMonth)
AND Month <= AsOfMonth
;