Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Number of days between dates

Hello!

I am having trouble comparing two dates. I'd like to get the difference in number of days between two dates. The problem right now is that it is returning the difference in hours. Say that I have a date1, 2011-10-01 18:00:00, and a date2, 2011-10-02 06:00:00. I would here like to get the result 1 day difference back. Instead, I get 12 hours difference. I tried using the interval, showing the difference on the format 'dd'. I thought it would automatically round the hours to one day, but instead it just shows 0.

Please help me find this out. I attach the file, where the two values to be compared are column 4 and 5, as can be seen in the expression 'Daily Diff'.

Thanks and regards.

1 Solution

Accepted Solutions
alfasierra
Contributor II

Number of days between dates

hi

you can use

dayStart(date) - dayStart(timestamp(Dateend)))

and set

in your script

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY';

4 Replies
Not applicable

Re: Number of days between dates

Why dont you choose to sum the dates instead of the whole timestamp?

LOAD Num(Year(FROM),'00','####') & '-' & Num(MONTH(FROM), '00', '##') & '-' & Num(DAY(FROM), '00', '##') as FROM_YearMonthDay,

           Num(Year(TO),'00','####') & '-' & Num(MONTH(TO), '00', '##') & '-' & Num(DAY(TO), '00', '##') as TO_YearMonthDay

sum(TO_YearMonthDay) - sum(FROM_YearMonthDay)

alfasierra
Contributor II

Number of days between dates

hi

you can use

dayStart(date) - dayStart(timestamp(Dateend)))

and set

in your script

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY';

Not applicable

Number of days between dates

Hi,

it may help you:

Data:
load id as ID,
timestamp#(date1,'YYYY-MM-DD hh:mm:ss') As DATE1,
  timestamp#(date2,'YYYY-MM-DD hh:mm:ss') As DATE2;
LOAD * INLINE [
    id,date1, date2
    1, 2011-10-01 18:00:00, 2011-10-02 06:00:00
];

left join(Data)
load ID,
  floor(DATE2) - floor(DATE1) As DIFF
Resident Data;

Not applicable

Number of days between dates

Thanks,

This works like a charm!

//Henrik

Community Browser