At the moment I have a straightforward application that uses a Payments table linked to a MasterCalendar linked through a date field (PaymentDate in the below example) and all works fine as expected in terms of showing Payments against any of the Date dimensions (Month-Year, Quarter, Week etc).
The requirement now call for intra-day - i.e. we need to introduce the "TIME" element to monitor patterns in the time of day that payments are made etc. This can be to the nearest minute (i.e. not interested in seconds and milliseconds) - the below (see PaymentTime) generates the time element in the hh:mm format.
Payments:
.
.
Date(Floor(CreatedTimestamp)) As PaymentDate,
Time(Floor(Frac(CreateTimestamp), 1/24/60), 'hh:mm') As PaymentTime,
.
.
My Payments table is currently linked to my MasterCalendar via "PaymentDate" - I am at a loss as to how to handle the time element.
Do I:
a) Create a separate "Time" table with say 3 fields PaymentTime, Hour, Minute and link it with the Payments table - if so how do I generate the "Time" table,
b) Extend the MasterCalendar to include time? If I do, how do I connect the Payments table with the extended Calendar.
Any help would be most appreciated.
Best regards
Alexis
