Dimension to display every hour over the last 24 hours

[Anonymous]

I'm attempting to show the number of incoming calls over the last 24 hours even if the value is zero.  My current dimension is:

     timestamp(round(inc_call_time,(1/24)))

This rounds the call times to the nearest hour and works properly but when I filter by a call type and that call type does not have a call during an hour, it doesn't show zero but it skips the hour.  I need to represent that hour with a zero.  I know it's doing this because my dimension calculates its values based on the inc_call_time field and that call type does not have a call within that hour.

Is there a dimension calculation I can use to display all hours in the last 24 hours not based on a time/date field in my data?

[Anonymous]

Hi Nick,

Check out this blog entry from Henric Cronstrom:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

[Anonymous]

Thanks! I think this will work with some modifications.  My data set has over 100k rows from a sybase server so I think if I create a new table with the dates in the range, a new date field in my SQL query, and join them I should be able to accomplish what I want.

I'll do some testing later today and let you know.