Even when it's against company policy to post your document, it can be very helpful if you post a sample QlikView document with a very simplified version of the data you're using, along with the results you expect. For instance, in the attached solution, I use this to generate some sample data:
Calls:
LOAD
recno() as ID
,timestamp(date#(20100801,'YYYYMMDD')+floor(rand()*12*24)/24) as Hour
AUTOGENERATE 10000
;
My suggested solution is to do something fairly complicated, which is to build a table that links today's hours to themselves AND to yesterday's hours, with flags letting you know what you're looking at:
AsOf:
// For each available hour, tie it to itself with the "Today?" flag.
LOAD
timestamp(fieldvalue('Hour',iterno())) as AsOfHour
,timestamp(fieldvalue('Hour',iterno())) as Hour
,1 as Today?
AUTOGENERATE 1
WHILE len(fieldvalue('Hour',iterno()))
;
// For each available hour, tie it to 24 hours ago with the "Yesterday?" flag.
CONCATENATE (AsOf)
LOAD
AsOfHour
,timestamp(AsOfHour-1) as Hour
,1 as Yesterday?
RESIDENT AsOf
;
// Only keep the connection to yesterday if the hour actually exists yesterday.
INNER JOIN (AsOf)
LOAD AsOfHour as Hour
RESIDENT AsOf
;
Then use AsOfHour as your table dimension instead of Hour, and use the two flags to control what you include in your count of calls. There are simpler solutions, but I think this is more robust than the simpler solutions that I'm aware of.
