Elapsed Time by Day, Across Multiple Days

[#1]

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.

[chematos]

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

[swuehl]

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