# 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

Tags (2)
1 Solution

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

Here is the expression:

Maybe you can post a sample qvf.

MVP

## 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

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

Contributor III

## Re: Date Calculations

if your date field has same format try networkdays()

Example:

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

networkdays (Date1,Date2)/365

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

Justin

Contributor III

## Re: Date Calculations

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

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