Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gauravgg
New Contributor III

difference between two dates (in Days or Hrs)

IDAmtDateFlag
110001/01/2016ORD
220002/01/2016ORD
110010/01/2016DEL
220011/01/2016DEL

I have the above table

i want the difference between dates

like Order Date (ORD) - Delivered Date(DEL)  in Days or Hrs

How can i get the Difference?

Can anyone help me ?

thanks in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: difference between two dates (in Days or Hrs)

Take care that your dates are correctly interpreted in your load script (e.g. by using the default format code

SET DateFormat = 'DD/MM/YYYY';      // or 'MM/DD/YYYY' ?

Then you can create a chart with dimension ID and as expression:

=Interval( Sum({<Flag = {ORD}>}Date) -Sum({<Flag = {DEL}>}Date), 'D')

resp.

=Interval( Sum({<Flag = {ORD}>}Date) -Sum({<Flag = {DEL}>}Date), 'h')


edit:

I think you want to calculate DEL-ORD, hence

=Interval( Sum({<Flag = {DEL}>}Date) -Sum({<Flag = {ORD}>}Date), 'D')


edit2: Replaced Only() with Sum(), so you can also get totals.

4 Replies
MVP
MVP

Re: difference between two dates (in Days or Hrs)

Take care that your dates are correctly interpreted in your load script (e.g. by using the default format code

SET DateFormat = 'DD/MM/YYYY';      // or 'MM/DD/YYYY' ?

Then you can create a chart with dimension ID and as expression:

=Interval( Sum({<Flag = {ORD}>}Date) -Sum({<Flag = {DEL}>}Date), 'D')

resp.

=Interval( Sum({<Flag = {ORD}>}Date) -Sum({<Flag = {DEL}>}Date), 'h')


edit:

I think you want to calculate DEL-ORD, hence

=Interval( Sum({<Flag = {DEL}>}Date) -Sum({<Flag = {ORD}>}Date), 'D')


edit2: Replaced Only() with Sum(), so you can also get totals.

gauravgg
New Contributor III

Re: difference between two dates (in Days or Hrs)

hi   swuehl

thanks for reply


but

how can i show in Report ?

how can i display the following table

id |    Amt  |    ORD Date     |  DEL Date    |   Difference between ORD and DEL Date

1  |    100  |   01/01/2016    |  10/01/2016  |     274

MVP
MVP

Re: difference between two dates (in Days or Hrs)

ID Amt Only({<Flag = {ORD}>}Date) =Only({<Flag = {DEL}>}Date) =Interval( Sum({<Flag = {DEL}>}Date) -Sum({<Flag = {ORD}>}Date), 'D')
548
110001/01/201610/01/2016274
220002/01/201611/01/2016274
gauravgg
New Contributor III

Re: difference between two dates (in Days or Hrs)

thanks