Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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 😊