Display missing hours of day when no data

[PhilMusgrave]

Hi,

I'm tasked with reporting employee activity by hour of the day, my issue is I'd like to display all hours of the day (00 - 23) when I don't have activity logged on every hour.

For example;

On a particular day I have activity logged between 1300 - 1900, see below. What I'd like is every hour of the day reported with 0 or null against the hours with no data.

I'm thinking calculated dimension, but everything I have tried hasn't worked.

Any help with this would be greatly appreciated.

Many thanks

Phil

[Mark_Little]

Hi there,

I would create a new Dimension in the script with an Inline Load.

HoursInDay:
LOAD * INLINE [
hour
00
01
... etc
];

This means the missing hours will now exist regardless of the data being against it. It will automatically link the new table on the dimension name hour.