Normalize aggregate using datetime hierarchy

[jmp]

Say I have two columns, Timestamp (datetime type) and TimeSpent (duration type represented in hours or whatever).

Now i want to calculate the total TimeSpent (Sum) in % using the Timestamp field as axis, creating a hierarchy over year, quarter, month, day. It is quite easy to calculate just the sum of the duration, but then i want to divide that number with the relevant level in the hierarchy, i.e. if the navigation point in the hierarchy is "day", i want to divide the duration with 24 hours and so forth.

The closest i can get is dividing with MAX(Timestamp)-MIN(Timestamp) within the "group", however this is not optimal since i have voids in the timeline in the original table.

Any idea how to solve this?

[TomMartens · Accepted solution]

Hey,

basically i would consider to create an additional calendar table that just contains dates.

Add an addtional column that inside your "fact" table that also just contains a date, without the time part.

Relate the tables with Calendar on the one-side and the fact table on the many-side.

Create a Measure that counts the timestamps, this measure than can be used as divisor for the sum of the durations.

This will work for a hierarchy like Day -> Month -> Quarter -> Year

If you need a more fine grained axis, than you have to build a more calendar/time table. Please be aware that you need a matching column in both of your tables, if the grain has to be an hour the values for the columns should look like this:

2018-08-14 00:00:00

2018-08-14 01:00:00

...

2018-08-14 23:00:00

2018-08-15 00:00:00

...

Hopefully this gives you an idea.

Regards,

Tom

[jmp]

Thank you very much for this tip.