Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

souha
New Contributor II

Cummulative sum for last X days

Hi,

I need to show for every day how many appliances are online for that day and the previous 7 days. Its basically cumulative sum but only for the previous 7 days.

Using this expression is shows the rolling sum and thats not what I need , I need it to calculate when I have 1,2,3,4,5 the result should be 1,3,6,10,15 and not 1,3,7,15,31

this is my expression and its doing rolling sum and not cummulative sum

rangesum(above(sum({$<Date=>} aggr(sum({$<Date=>} [unique_appliance_count]),[unique_appliance_count])),0,rowno()))

2 Solutions

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Cummulative sum for last X days

replace the rowno() function with the number of days you want to sum over: rangesum(above(sum({$<Date=>} aggr(sum({$<Date=>} [unique_appliance_count]),[Month-Year])),0,7))


talk is cheap, supply exceeds demand
MVP & Luminary
MVP & Luminary

Re: Cummulative sum for last X days

Try aggregating over Month-Year instead of unique_appliance_count

talk is cheap, supply exceeds demand
5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Cummulative sum for last X days

replace the rowno() function with the number of days you want to sum over: rangesum(above(sum({$<Date=>} aggr(sum({$<Date=>} [unique_appliance_count]),[Month-Year])),0,7))


talk is cheap, supply exceeds demand
HirisH_V7
Honored Contributor

Re: Cummulative sum for last X days

use some thing like this:

Sum({<Date={">=$(=Date(Max(Date)-7))<=$(=Date(Max(Date)))"}>}Sales)
HirisH
“Aspire to Inspire before we Expire!”
souha
New Contributor II

Re: Cummulative sum for last X days

Thank you. But I tried that by putting 3 instead of rowNo and results are strange.

Above is the number of connected units and below it the accumulated count and numbers are wrong.

Screenshot 2019-04-17 at 12.52.01.png

MVP & Luminary
MVP & Luminary

Re: Cummulative sum for last X days

Try aggregating over Month-Year instead of unique_appliance_count

talk is cheap, supply exceeds demand
souha
New Contributor II

Re: Cummulative sum for last X days

Yes! That was the trick. Thank you!!