Date Difference in Qlik Sense

[Anonymous]

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

[sasiparupudi1 · Accepted solution]

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

[sasiparupudi1]

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

[jonathandienst]

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)

[Anonymous]

Hi Jonathan,

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

Thanks

Neha

[Anonymous]

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

Thanks,

Neha

[jonathandienst]

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