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?

[marcus_sommer]

Too much brackets, conversions and other syntax. Just try:

interval([Date Initiated] - today(), 'M') as X

If it doesn't returned the expected value just use parts of it to find those ones which have no respectively a different result, maybe:

date([Date Initiated]) as Y
[Date Initiated] - today() as Z

[Chris_Buck]

Thank you the following did not work, no data showed

interval([Date Initiated] - today(), 'M')

How would I rewrite it?

[marcus_sommer]

What return the parts? Like:

date([Date Initiated]) as Y
[Date Initiated] as YY

[Chris_Buck]

I'm unsure what you mean?

I entered interval this ([Date Initiated] - today(), 'M')

[TauseefKhan]

Hi @Chris_Buck 
Check this:
Interval(date#([Date Initiated], 'DD-MM-YYYY') - Today(), 'M')

[marcus_sommer]

You need to check the data and their types (numbers or strings) and their formatting at first - if they are known you could address them properly.

I just looked in the help and interval() doesn't supports months/years as returns else only days and times. This means you couldn't use it. Alternatively approaches may be something like:

round((Date1 - Date2) / 30, 0.1) as X

or

(year(Date1) *12 + month(Date1)) - (year(Date2) *12 + month(Date2))  as Y

[Chris_Buck]

@Tauseef it says the expression is OK, but it doesn't bring me any values

This is how the data is displaying in the app. 

I am entering the function in the Open & Overdue/Long Overdue column, which I am creating as a dimension.

[marcus_sommer]

Your date-field is left-aligned which hints that's not a real date else a string. To convert a string to a number you will need to specify the correct format-pattern and ensuring that the interpretation-variables are equally set to them. In your case the conversion may look like:

date(date#([Date Initiated], 'MMM DD, YYYY'), 'DD-MM-YYYY') as [Date Initiated]

and such measurement should be done ones within the script.

[Chris_Buck]

A lot of jargon for me @marcus_sommer 😅

Are you saying then that I should enter:  date(date#([Date Initiated], 'MMM DD, YYYY'), 'DD-MM-YYYY')

in the field expression?

Will it give me the months? I need the how many months value between the dates like the excel function =DATEDIF(Cell Reference,TODAY(),"M") does