Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Buck
Contributor III
Contributor III

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)
1 Solution

Accepted Solutions
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)

View solution in original post

14 Replies
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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

I'm unsure what you mean?

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

 

TauseefKhan
Creator III
Creator III

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
Contributor III
Contributor III
Author

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

Chris_Buck_1-1718283072087.png

 

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
Contributor III
Contributor III
Author

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