calculate (date-time) difference

[Anonymous]

Hi

I have 2 dates with time stamp of the format 'dd/mm/yyyy hh:mm:ss'. I need to calculate the difference between the 2 in seconds and then convert it back to days because in between the 2 dates, I may have bank holidays and weekends which need not be considered in the difference between the 2 dates. however, the final date time difference must still be in the format 'days hours minutes seconds..

I would like to know if this is possible in Qlikview.  Kindly provide me with an example if it is possible

Many thanks

Hasnaa

[MarcoWedel]

Hi,

if your start and end dates are always working days, then one solution could be also:

tabDates:
LOAD *,
    Interval(Frac(DateTimeEnd)-Frac(DateTimeStart)-1+NetWorkDays, 'd hh:mm:ss') as WorkInterval;
LOAD *,
    NetWorkDays(DateTimeStart, DateTimeEnd) as NetWorkDays,
    Interval(DateTimeEnd-DateTimeStart, 'd hh:mm:ss') as Interval
Where WeekDay(DateTimeStart)<=4 and WeekDay(DateTimeEnd)<=4;
LOAD *,
    Timestamp(DateTimeStart+Rand()*20) as DateTimeEnd;
LOAD Timestamp(MakeDate(2014)+Rand()*200) as DateTimeStart
AutoGenerate 100;

hope this helps

regards

Marco