cancel
Showing results for
Did you mean:

# Time Calculations in Sequence of Events

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:

## 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:
Version history
Last update:
‎03-02-2023 08:28 AM
Updated by:
Contributors
Community Toolbox

Developers Group:

Product Feedback Forum: