This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
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:
