cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

 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.

 Modeling Challenge

  1. Orders fact table has 3 fields: Order ID, Status, Date.
  2. The order process is consisted of 3 steps: creation, packaging and shipment.
  3. Each entity in the fact table has only the date of the step it records. 
Original Fact table:
1.jpg

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.jpg
3.jpg
2. Create key for current step by concatenating the Order ID and the Rank:
4.jpg
5.jpg
3. Create key for the previous step by concatenating the Order ID and the Rank of the previous step, which is the StepRank-1
6.jpg
7.jpg
4. Lookup the date of the previous step within the same fact table by matching on the previous step key and the step key:
8.jpg
9.jpg
5. Calculate the days difference between the date and the previous step date to get the duration of the task:
10.jpg
11.jpg
Rate this article:
Version history
Last update:
‎03-02-2023 08:28 AM
Updated by:
Contributors