Solution for 24 hours days over two dates?

[#1]

Hi,

I have a report where we show number of sales (example) each day.

• You can make selections by year, month, week, day and hour, and see the quantity for each of these selections.
• Usually if i wanted to check the quantities for i.e. september 25th 2011, this would incude all 24hours within this day.
• In this case our day starts at 7 am and ends at 6:59 am the next morning. This means that if im going to count the quantity for september 25th this will include the last 17 hours on the 25th and the first seven hours of the 26th of september
• (you could say we have "fiscal days"

Is there any best practices for solving issues like these??

[swuehl]

You could probably just subtract 7 hours from your time stamps, creating your "fiscal days", then use the date part only (or use the time fraction part as offset from your day start), like

LOAD

....

Daystart(YOURTIMESTAMPFIELD- 7/24) as ShopDate,

....

from ...

YOURTIMESTAMPFIELD should be a recognized QV date time field, maybe you need to apply timestamp#() first.

Hope this helps,

Stefan