Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Valued Contributor II

Re: Date Calculations

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

9 Replies
sinanozdemir
Valued Contributor III

Re: Date Calculations

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.

Re: Date Calculations

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)

MVP
MVP

Re: Date Calculations

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

Re: Date Calculations

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

Re: Date Calculations

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

Not applicable

Re: Date Calculations

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

Re: Date Calculations

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

martinpohl
Valued Contributor II

Re: Date Calculations

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

Re: Date Calculations

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

Community Browser