Cumulative Sales by Month but only display balances as of selected Months

[Anthony]

Hi Everyone,

Have an issue to solve and need the set analysis experts.

My back end data source is pretty much fixed and I have a need to be able to show cumulative sales amounts for each month but also need to be able to restrict which months are viewable in my chart (don't want to just plaster them all up there).  So think of a Line Chart where only Months 7,8,9 are shown even though the sales balances themselves for Month's 7,8,9 take into account the sales from prior months (because we want to show cumulative sales).

Example:  Bill's Month 7 cumulative balance should be 280

From reading a number of other threads I came up with this code but it won't give me the right number once I set the filter to be only Months 7,8,9.  If I change the code to have it ignore the Month filter selection then it will just show every month's cumulative YTD sales in the Line chart.  How do I solve this?

= RangeSum(Above(TOTAL Sum(MonthlySales),0,Rowno()))

Example of table format/data

Month	Customer	MonthlySales
1	Bill	100
1	Chuck	50
1	Frank	75
2	Bill	25
2	Chuck	30
2	Frank	50
3	Bill	40
3	Chuck	25
5	Bill	15
5	Chuck	45
5	Frank	65
6	Chuck	100
6	Frank	75
6	Amy	80
7	Bill	100
7	Chuck	50
7	Frank	75
8	Bill	25
8	Chuck	30
8	Frank	50
9	Bill	40
9	Phil	25
11	Bill	15
11	Chuck	45
11	Frank	65
12	Chuck	100
12	Frank	75
12	Amy	80

Thanks

[lironbaram · Accepted solution]

Hi 

try this expression 

=sum({<Month={"<$(=min([Month]))"}>}total MonthlySales) + RangeSum(Above(TOTAL Sum(MonthlySales),0,Rowno()))

[Gysbert_Wassenaar]

See this document for a discussion of this kind of problem: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...