Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2017-06-01 11:08 AM

Rolling / cumulative sum of days

Hello,

I calculated Active Days with the following expression:

Monthend(Max( [TRANS_DATE]))-

Monthstart(Min([TRANS_DATE]))

Now I want to have a running total for active days, see the last column below:

     

CUSTOMER_IDRTI_NAMEYearMonthYearActive Days
762
1000009371Prod x201706-201730762
1000009371Prod x201705-201731732
1000009371Prod x201704-201730701
1000009371Prod x201703-201731671
1000009371Prod x201702-201728640
1000009371Prod x201701-201731612
1000009371Prod x201612-201631581
1000009371Prod x201611-201630550
1000009371Prod x201610-201631520
1000009371Prod x201609-201630489
1000009371Prod x201608-201631459
1000009371Prod x201607-201631428
1000009371Prod x201606-201630397
1000009371Prod x201605-201631367
1000009371Prod x201604-201630336
1000009371Prod x201603-201631306
1000009371Prod x201602-201629275
1000009371Prod x201601-201631246
1000009371Prod x201512-201531215
1000009371Prod x201511-201530184
1000009371Prod x201510-201531154
1000009371Prod x201509-201530123
1000009371Prod x201508-20153193
1000009371Prod x201501-20153162
1000009371Prod x201412-201431

Can somebody explain how to do this?

Kind regards,

Erik

393 Views
0 Likes
1 Reply
settu_periasamy
Master III
Master III
‎2017-06-01 02:45 PM

Try this.

sum(TOTAL MonthEnd([TRANS_DATE])-MonthStart([TRANS_DATE]))-Rangesum(Above(TOTAL MonthEnd([TRANS_DATE])-MonthStart([TRANS_DATE]),1,RowNo(TOTAL)),0)

323 Views
0 Likes