Interval between dates not working

[Chris_Buck]

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?

[Chris_Buck]

FYI:

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

I do get values, see pic below.

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]

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

I do get values, see pic below.

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

[marcus_sommer · Accepted solution]

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]

Thank you so much Marcus! That worked a treat 😊