topic Elapsed Time by Day, Across Multiple Days in QlikView

Elapsed Time by Day, Across Multiple Days

Fri, 11 May 2012 18:25:57 GMT

I have a simple data set that looks like this:

(Sorry, but the "Insert Table" function isn't working for me.)

AccountNumber StartDateTime EndDateTime

A123456 1/1/2012 16:00 1/1/2012 18:25

A123457 1/1/2012 16:00 1/2/2012 08:15

A123458 1/1/2012 16:00 1/3/2012 14:45

I need to be able to calculate the number of hours for each day. In this example, the daily totals would be:

1/1/2012 = 18.42 hours

1/2/2012 = 32.25 hours

1/3/2012 = 14.75 hours

I'm sure I'm not the first one to encounter this type of issue. Would this be done in the script, in the expression, or a combination of the two?

Thanks.

Elapsed Time by Day, Across Multiple Days

chematos — Fri, 11 May 2012 20:52:23 GMT

Hi,

I would do it in the script load.

Load *

AccountNumber,

StartDateTime,

EndDateTime,

If(Day(StartDateTime)=Day(EndDateTime),EndDateTime-StartDateTime,Floor(EndDateTime)-StartDateTime) as Hours_Day

from table;

I don´t know if Floor(EndDateTime)-StartDateTime will work but If won´t work is because the types of the dates are different. I think this is the best way to get what you want but probably are better options.

Hope this helps

Elapsed Time by Day, Across Multiple Days

swuehl — Fri, 11 May 2012 22:59:28 GMT

I think you can do it just like I posted here, using a while loop to iterate over the days in your intervals and a complicated-looking conditional to calculate the hours for eah single day.

http://community.qlik.com/message/152379