Time Calculations in Sequence of Events
Analytical Need
Calculate the time difference between two events in a fact table.
In the example, we want to measure how many days it took to complete each task in a shipment process.
In the example, we want to measure how many days it took to complete each task in a shipment process.
Modeling Challenge
- Orders fact table has 3 fields: Order ID, Status, Date.
- The order process is consisted of 3 steps: creation, packaging and shipment.
- Each entity in the fact table has only the date of the step it records.
Original Fact table:
Solution
After configuring a couple of added fields, we can use the Lookup function to bring in the date of the previous event in a single table. Next, we can simply calculate the difference in days using DayDiff.
Steps:
1. Rank all the events based on their date with partition by the Order ID:
2. Create key for current step by concatenating the Order ID and the Rank:
3. Create key for the previous step by concatenating the Order ID and the Rank of the previous step, which is the StepRank-1
4. Lookup the date of the previous step within the same fact table by matching on the previous step key and the step key:
5. Calculate the days difference between the date and the previous step date to get the duration of the task:
Updated 03-02-2023
