Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

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

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

MonthCustomerMonthlySales
1Bill100
1Chuck50
1Frank75
2Bill25
2Chuck30
2Frank50
3Bill40
3Chuck25
5Bill15
5Chuck45
5Frank65
6Chuck100
6Frank75
6Amy80
7Bill100
7Chuck50
7Frank75
8Bill25
8Chuck30
8Frank50
9Bill40
9Phil25
11Bill15
11Chuck45
11Frank65
12Chuck100
12Frank75
12Amy80

 

Thanks

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

Hi 

try this expression 

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

View solution in original post

2 Replies
Highlighted
Partner
Partner

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

Hi 

try this expression 

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

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand