Cumulative Sum by Month - QlikSense

[Ceb]

Hi,

I am trying to use RangeSum to get cumulative sum of a column by the month. I tried the Aggr function along with the RangeSum function but not able to do so 

This is what I have right now

Date	Actual	Year Target	Month Target
1/1/2019 0:00	0	1848	80
1/2/2019 0:00	4	1848	80
1/3/2019 0:00	12	1848	80
1/4/2019 0:00	17	1848	80
1/5/2019 0:00	17	1848	80
.	.	.	.
.	.	.	.
.	.	.	.
2/1/2019 0:00	118	1848	92
2/2/2019 0:00	118	1848	92
2/3/2019 0:00	118	1848	92
2/4/2019 0:00	127	1848	92
2/5/2019 0:00	127	1848	92
.	.	.	.
.	.	.	.
.	.	.	.
3/1/2019 0:00	217	1848	88
3/2/2019 0:00	217	1848	88
3/3/2019 0:00	217	1848	88
3/4/2019 0:00	223	1848	88
3/5/2019 0:00	229	1848	88

For the month Target I have 80,92,88 which is individual Month Target but I need to do a vlookup on the month and if new month I need the cumulative sum

I need the following output

Date	Actual	Year Target	Month Target
1/1/2019 0:00	0	1848	80
1/2/2019 0:00	4	1848	80
1/3/2019 0:00	12	1848	80
1/4/2019 0:00	17	1848	80
1/5/2019 0:00	17	1848	80
.	.	.	.
.	.	.	.
.	.	.	.
2/1/2019 0:00	118	1848	172
2/2/2019 0:00	118	1848	172
2/3/2019 0:00	118	1848	172
2/4/2019 0:00	127	1848	172
2/5/2019 0:00	127	1848	172
.	.	.	.
.	.	.	.
.	.	.	.
3/1/2019 0:00	217	1848	260
3/2/2019 0:00	217	1848	260
3/3/2019 0:00	217	1848	260
3/4/2019 0:00	223	1848	260
3/5/2019 0:00	229	1848	260

Thanks

[StarinieriG]

Hi,

try with this expression:

RangeSum(Above(TOTAL Aggr(Max([Month Target]),[Month Target]),0,RowNo(total)))

[Ceb]

Thanks for the reply StarinieriG!!

Not able to get the answer though.

For the monthly target I use the following code.

=floor(Max(Target)/
(networkdays(MonthStart([Final.dateenrolled]) ,MonthEnd([Final.dateenrolled]))))

Max(target) - Yearly Target

networkdays(MonthStart([Final.dateenrolled]) ,MonthEnd([Final.dateenrolled]))) - number of working days

[sunny_talwar]

May be this

RangeSum(Above(If(Day(Date) = 1, Sum(DISTINCT [Month Target]), 0), 0, RowNo()))

[Ceb]

Thanks for the reply Sunny!!

In my case instead of [Month Target] I use the following 

Max(Target)/
networkdays(MonthStart([Final.dateenrolled]) ,MonthEnd([Final.dateenrolled]))

So implementing your code in mine 

RangeSum(Above(If(Day([Date]) = 1, Sum(DISTINCT

Max(Target)/
networkdays(MonthStart([Date]) ,MonthEnd([Date]))

), 0), 0, RowNo()))

I get an error in expression.

Is there an issue using Distinct and Max together?

[sunny_talwar · Accepted solution]

How about this

RangeSum(Above(If(Day(Date) = 1, Max(Target)/NetWorkDays(MonthStart([Final.dateenrolled]), MonthEnd([Final.dateenrolled])), 0), 0, RowNo()))