Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
sruthi19
Contributor II
Contributor II

Cummulative sum based on the date

Hi Team,

Please help me out -

I have month , createDate, Closeddate  fields

MonthCreateDateClosedDatefloor(ClosedDate - CreateDate
April01-04-202102-04-20211
April10-04-202120-04-202110
May01-05-202104-05-20213
May21-05-202126-05-20215
June06-06-202106-06-20210
June02-06-202130-06-202128

 

Output :

I want the cumulative sum (floor(ClosedDate - CreateDate)  for the related month.

MonthCumulative Sum
April11
May8
June28
Labels (1)
6 Replies
Quy_Nguyen
Specialist
Specialist

Hi,

Try this expression for your cummulative sum: 

Sum(Aggr(Date#(ClosedDate,'DD-MM-YYYY') - Date#(CreateDate,'DD-MM-YYYY'), CreateDate))

 

Quy_Nguyen_0-1624530948867.png

 

sruthi19
Contributor II
Contributor II
Author

Hi Nguyen,

 

Thanks for your response. But it did not worked for me.

 

sruthi19_0-1624596317047.png

 

Quy_Nguyen
Specialist
Specialist

Please take a look about your date format. In the given data in original post, its format is different. So you may need to change the Date# format in the expression to match with your format.

sruthi19
Contributor II
Contributor II
Author

in original post i have given the sample data, but if you see the screenshot i have provided the right Date# format only - MM-DD-YYYY

sruthi19_0-1624613381265.png

 

Quy_Nguyen
Specialist
Specialist

Are you sure? I meant your date format in the app is M/D/YYYY.  

Quy_Nguyen_0-1624618449712.png

Try to make them using the same format. Or if it is already a valid date, use this:

Sum(Aggr(ClosedDate - CreateDate, CreateDate))

And remove the columns ClosedDate, CreateDate from the table - like the table you expect in the post

 

sruthi19
Contributor II
Contributor II
Author

Thanks it worked.

Sorry for the late response