Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Buck
Contributor II
Contributor II

Interval between dates not working

Hello,

I am new to Qliksense, so I'm hoping I am not repeating a question that has already been asked.

I currently extract the data from Qliksense and enter the this formula in excel =DATEDIF("Date",TODAY(),"M")

"Date" is a cell where date is in DD-MM-YYYY.

 

I want this information available in Qliksense do I don't need to extract it, so I created a dimension and entered the following

Interval((date#(([Date Initiated],'DD-MM-YYYY')) -(date#(Today(),'DD-MM-YYYY')),"M")

But I am getting an error. How do I fix it?

Labels (1)
14 Replies
Chris_Buck
Contributor II
Contributor II
Author

FYI:

If I put =[Date Initiated]-Today() 

I do get values, see pic below.

Chris_Buck_0-1718286014300.png

 

So, what I need is those values to be in months, I hope I am making sense 😅

 

marcus_sommer

No, it won't return the months else it is aimed to get a real date as basis for everything else. A real date is a number - starting with 1 on the 01-01-1900 and each day from it will be add 1 to it (any formatting is only for an easier readability).

Only with a real date you will be able to derive years/months and so on from it and to add n days/periods and/or to compare a date with >= or <= against another one. Therefore it's the basic-work which should be done ones within the script. 

Chris_Buck
Contributor II
Contributor II
Author

I'm unsure if you saw the reply above yours. But if I put =[Date Initiated]-Today() 

I do get values, see pic below.

Chris_Buck_0-1718291873149.png

 

 

So, what I need is those values to be in months. What do I put the expression as?

 

 

 

marcus_sommer

It's the difference in days. I assume you won't show them in this direction as negative values which could be easily reversed and then dividing it by 30 days and applying an appropriate rounding, maybe like:

floor((Today() - [Date Initiated]) / 30)

floor() is rounding down - ceil() would round up and if you want to show digits instead of integers then add the wanted ones to the rounding, like: floor(Value, 0.1)

Chris_Buck
Contributor II
Contributor II
Author

Thank you so much Marcus! That worked a treat 😊