topic Getting the difference in two dates in App Development

Getting the difference in two dates

Evan0211 — Tue, 09 Nov 2021 14:16:17 GMT

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?

Re: Getting the difference in two dates

Or — Tue, 09 Nov 2021 14:26:15 GMT

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'

Re: Getting the difference in two dates

stevejoyce — Tue, 09 Nov 2021 14:27:10 GMT

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])

Re: Getting the difference in two dates

Evan0211 — Tue, 09 Nov 2021 15:12:16 GMT

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?

Re: Getting the difference in two dates

Or — Tue, 09 Nov 2021 17:33:24 GMT

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