Date Calculations

[#1]

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

[sinanozdemir]

Hi Justin,

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

Here is the expression:

Maybe you can post a sample qvf.

[robert_mika]

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]

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]

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]

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

[#7]

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]

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

[martinpohl · Accepted solution]

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

[#10]

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