topic Re: Interval between dates not working in App Development

Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 10:22:55 GMT

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?

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 11:27:15 GMT

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

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 12:19:22 GMT

Thank you the following did not work, no data showed

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

How would I rewrite it?

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 12:24:07 GMT

What return the parts? Like:

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

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 12:32:26 GMT

I'm unsure what you mean?

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

Re: Interval between dates not working

TauseefKhan — Thu, 13 Jun 2024 12:39:51 GMT

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

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 12:50:05 GMT

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

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

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 12:52:38 GMT

@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.

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 13:01:08 GMT

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.

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 13:06:39 GMT

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

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 13:41:21 GMT

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 😅

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 13:41:45 GMT

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.

Re: Interval between dates not working

Chris_Buck — Thu, 13 Jun 2024 15:19:02 GMT

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?

Re: Interval between dates not working

marcus_sommer — Thu, 13 Jun 2024 15:29:02 GMT

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)

Re: Interval between dates not working

Chris_Buck — Thu, 20 Jun 2024 10:26:35 GMT

Thank you so much Marcus! That worked a treat 😊