# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

## Calculate Cumulative Sum

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.

Regards,

Rahul

Tags (1)
11 Replies
Not applicable

## Calculate Cumulative Sum

HI dear

use the following function :

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

this will help

Employee

## Calculate Cumulative Sum

Hello Rahul,

Following your table, the following expression should work

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

Is this what you are looking for?

Not applicable

## Calculate Cumulative Sum

Hi! Miguel/ Rishi

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

MVP

## Calculate Cumulative Sum

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:
Month as AsOfMonth
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).

Not applicable

## Calculate Cumulative Sum

Hi! John,

Thanks a lot, its working prefectly

Regards,

Rahul

Employee

## Calculate Cumulative Sum

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.

MVP

## Calculate Cumulative Sum

`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.

Not applicable

## Calculate Cumulative Sum

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.

Amey

MVP

## Calculate Cumulative Sum

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

[AsOf]: