topic Re: Date Difference in Qlik Sense in QlikView

Date Difference in Qlik Sense

Anonymous — Wed, 19 Aug 2015 08:10:23 GMT

Hi All,

I have a transaction csv file which has two fields TR_PRCS_DATE and TR_TXN_DATE. I want to calculate the difference between these two dates in number of days in my qlikview/qliksense script. I have already trieD the following formulas but these did not work -

Interval([To Date]-[From Date],'d')

Interval( (date((TR_PRCS_DATE),'DD/MM/YYYY')) - (date(TR_TXN_DATE,'DD/MM/YYYY')), 'd') as DTDiff // (In script).

Attached is the TRANSACTION.csv.

Any help would be much appreciated.

Thanks,

Neha

Re: Date Difference in Qlik Sense

sasiparupudi1 — Wed, 19 Aug 2015 08:15:25 GMT

Interval( (date#((TR_PRCS_DATE),'DD-MMM-YY')) - (date#(TR_TXN_DATE,'DD-MMM-YY')), 'd') as

Re: Date Difference in Qlik Sense

sasiparupudi1 — Wed, 19 Aug 2015 08:16:45 GMT

Basically you need to use date# to tell qlikview about the date format of your date string ..It can then interpret it as a correct date and will give you the difference properly

hth

Sasi

Re: Date Difference in Qlik Sense

jonathandienst — Wed, 19 Aug 2015 08:19:34 GMT

If the two fields are proper QV date/timestamp values, then you can use

TR_PRCS_DATE - TR_TXN_DATE

If you want just the integer number of days, then

Floor(TR_PRCS_DATE - TR_TXN_DATE)

Round(TR_PRCS_DATE - TR_TXN_DATE)

Floor(TR_PRCS_DATE) - Florr(TR_TXN_DATE)

(depending on which best suits yoour requirement)

Re: Date Difference in Qlik Sense

Anonymous — Wed, 19 Aug 2015 08:23:15 GMT

Hi Jonathan,

Thanks for responding. But these functions are not working may be due to format issue.

Thanks

Neha

Re: Date Difference in Qlik Sense

Anonymous — Wed, 19 Aug 2015 08:24:17 GMT

Thank you so much Sasidhar. Your solution is the correct approach.

Thanks,

Neha

Re: Date Difference in Qlik Sense

jonathandienst — Wed, 19 Aug 2015 08:28:08 GMT

Complete load:

LOAD *,

Floor(TR_PRCS_DATE - TR_TXN_DATE) As INTERVAL;

LOAD Date(Date#(TR_CYCLE_DATE, 'dd-MMM-YY')) As TR_CYCLE_DATE,

TR_TXN_NUM,

TR_POL_NUM,

TR_TXN_TYPE,

TR_TXN_STATUS,

Date(Date#(TR_TXN_DATE, 'dd-MMM-YY')) As TR_TXN_DATE,

Date(Date#(TR_PRCS_DATE, 'dd-MMM-YY')) As TR_PRCS_DATE,

TD_TXN_UNITS,

TD_FUND_CODE

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);