Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Calculations

Hello,

I am wondering if there is a way in Qlik Sense that I can create an expression that takes two date fields and does a mathematical calculation with them to then output the measure of a graph.

The calculation is fairly simple, as it would be   (Date1 - Date2) / 365

It just seems currently I am unable to do this, I think the output is null as I just get a flat line in my line graph. It makes sense because Dates are not whole numbers so logically it makes no sense to minus them or divide them. So if anyone has any input on how I can make this expression work I would be very greatful.

Thanks,

Justin

1 Solution

Accepted Solutions
martinpohl
Partner - Master
Partner - Master

your expression is missing an aggregation because there are many dates to calculate, but what to do with?

Sum(([REPORT DT] - [INCIDENT DT])/365)

or

Avg(([REPORT DT] - [INCIDENT DT])/365)

or

Max(([REPORT DT] - [INCIDENT DT])/365)

Regards

View solution in original post

9 Replies
sinanozdemir
Specialist III
Specialist III

Hi Justin,

I am not sure about your date formats, but you can simply deduct two dates as shown below:

Capture.PNG

Here is the expression:

Capture.PNG

Maybe you can post a sample qvf.

robert_mika
Master III
Master III

Dates are whole numbers.What you see is just the format.

So to example 40250  is 13/03/2010 and today's date is 42305.

If your Date1 is less that Date2 you will get values less that 0 -divide that by 365 you will get fraction.

Post your data or qvf file to see what is going on

Feeling Qlikngry?

How To /Missing Manual(18 articles)

jagan
Luminary Alumni
Luminary Alumni

Hi Justin,

Try like this, Qlikview internally stores dates as numbers.

(Num(Date1) - Num(Date2)) / 365


If dates are not in date format then you have convert it by using Date#() like below


(Date#(Date1, 'MM/DD/YYYY') - Date#(Date2, 'MM/DD/YYYY')) / 365


Replace MM/DD/YYYY with your actual date format.



Hope this helps you.


Regards,

jagan.

ahmar811
Creator III
Creator III

if your date field has same format try networkdays()


Example:

networkdays ('2007-02-19', '2007-03-01') returns 9

Your Expression like these:

networkdays (Date1,Date2)/365

raju_insights
Partner - Creator III
Partner - Creator III

Try, (Num(Date1)-Num(Date2)) / 365

Not applicable
Author

Thank you all for your quick responses. I have tried each of your suggestions but no luck, I still just get a flat line. The date fields I am using are currently in Date format via Excel. So Im not sure why its not working. Unfortunately the data I am using is confidential so I wouldn't be able to post the full application. But I have pulled just what is used for this chart for a sample qvf file, uploaded to google drive - Date Calc Test.qvf - Google Drive

Thanks for your assistance,

Justin

raju_insights
Partner - Creator III
Partner - Creator III

Try this, Sum(([REPORT DT] - [INCIDENT DT])/365)

martinpohl
Partner - Master
Partner - Master

your expression is missing an aggregation because there are many dates to calculate, but what to do with?

Sum(([REPORT DT] - [INCIDENT DT])/365)

or

Avg(([REPORT DT] - [INCIDENT DT])/365)

or

Max(([REPORT DT] - [INCIDENT DT])/365)

Regards

Not applicable
Author

Oh my....one of those moments for me I guess. I completely missed out on the aggregate function. I need to apply average to my date fields. Well thanks everyone for helping me realize this. *facepalm*

Thanks,

Justin