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: 
Evan0211
Creator
Creator

Getting the difference in two dates

I have a table that is set up like this

Event Planned Date Revised Date Completed Date
Fact Finding xxx xxx xxx
Customer Contact xxx xxx xxx
Bill xxx xxx xxx

I need to calculate the difference between the planned fact finding and the planned billing date.

Also, the dates can be null as they are being developed.  I was thinking it would be something like:

if the event is = fact finding and planned date isn't null and event = bill and planned date isn't null, calculate, but I don't know how to write that in Qlik.  Any suggestions? 

1 Solution

Accepted Solutions
stevejoyce
Specialist II
Specialist II

The calculation shouldn't work if one of the values are null, so you really don't need to add specific scenario for it.  Front-end could be something like this.

only({<Event = {'Fact Finding'}>} [Planned Date]) - only({<Event = {'Bill'}>} [Planned Date])

View solution in original post

4 Replies
Or
MVP
MVP

If that's your entire table (that is, there's only ever going to be one line each for each event name), if you want to do this in an expression on the front end,

Only({< Event = {'Fact Finding'} >} Planned Date) - Only({< Event = {'Bill'} >} Planned Date)

If either of these null, the result will be null (mathematical actions on null result in null).

You could also just load the information into a single line scriptside, e.g.

Load Event, Planned Date, Revised Date, Completed Date

From Table

Where Event = 'Fact Finding'

JOIN

Load Event, Planned Date as [Bill Planned Date]

From Table

Where Event = 'Bill'

stevejoyce
Specialist II
Specialist II

The calculation shouldn't work if one of the values are null, so you really don't need to add specific scenario for it.  Front-end could be something like this.

only({<Event = {'Fact Finding'}>} [Planned Date]) - only({<Event = {'Bill'}>} [Planned Date])

Evan0211
Creator
Creator
Author

This my be a stupid question so I am sorry in advance, but will this run in a KPI or would I need to wrap it in a sum?

Or
MVP
MVP

Only() is an aggregation function, so assuming there's only one value here, you shouldn't need anything else.