How to flag periods between two dates

Center of Excellence
Center of Excellence Employee
500 Likes 10 Comments First Anniversary Name Dropper
edited January 7 in How-To Guides
1088x612_Article_Cover_How-to-Guide.png

1. Abstract

In some cases, calculations are only valid within a defined time period with a specific start and end date. Therefore, it’s crucial to first identify this period to ensure the calculation is applied accurately.

2. Context

Many business processes rely on calculations between two dates. For example, calculating the impact of a specific promotion or discount, tracking HR events such as lateral moves effective from a certain date, or applying depreciation starting from a defined date. These are all examples where precise date calculations are essential.

3. Content

3.1 Flag All Period between Start and End Date

To address the challenge of flagging all periods between two dates, there are different approaches we can follow. The example below shows one of them.

The table below shows an example of sample data where for each promotion, we have the following details:

  • Start Date
  • End Date
  • Product category
image.png

The desired output is the following:

For each Promotion and product category, flag all the periods where the promotion is valid. This can be applied to any context with a similar request.

image.png

To address the challenge, below is a step-by-step breakdown of one possible approach.

1. Load Start and End Dates into Cubes

The first step is to load the start and end dates into date-type cubes with all the relevant dimensions.

In this example, we will need the start and the end date cubes dimensioned by Promotion and product category.

2. Use Dataflow Absolute Time Offset

Once the dates are loaded into the start and end date cubes, use the dataflow Absolute time offset function to shift these date values into the time dimension appropriately. These flags will be stored in two new integer cubes: Start Date Flag Cube and End Date Flag Cube.

image.png

Focusing on one promotion in particular this is the situation after this step:

image.png

3. Calculating the Discount Period Range

Now that we have the adjusted start and end dates, we can proceed with a series of calculations to define the promotion period. That means flagging all the periods between the start and the end date.

The desired output is the following:

image.png

To get the result in column three:

  • Create the target cube Promotion All Periods Flag dimensioned by Product category, promotion and month.
  • Create a dataflow to calculate the target cube as follows: c=a-b. Where:
    • a is the Promotion Start Date flag cube with the YMT applied combined with the Cycle function.
    • b is the Promotion End Date flag cube with the YMT applied combined with the Cycle function and the offset -1 to include the end date in the range. In the example above, Apr 2024 won’t be part of the range calculated without the offset.

In both cases, make sure the cycle is set high enough to calculate the value until the end of the time range.

Another approach to obtain the same result can be the following:

Create a TEMP cube with same dimensions as the two cubes Promotion start date Flag and Promotion End Date Flag.

Create the following procedure:

  • Dataflow 1:
  • Temp cube = Promotion start date Flag - Promotion End Date Flag (with Offset -1) applied

Like before, the offset -1 is to include in your range also the end date.

  • Dataflow 2
  • Promotion All Periods Flag = if(a>=0,a,0) - where a is the temp cube calculated in the dataflow 1 with the last value applied → This is writing 1 from the start to the end date, included.

3.2 Flag All Periods until an End Date

In this case the process is very similar to the previous. The main difference is that the “Start Date” is not a dynamic information based on the details (product category, promotions) but it’s fixed based on the data model time range. The start date flag cube will be a technical cube that flags the first month of the data model time range. The rest of the process is exactly the same as explained for Case 1.

3.3 Flag All Periods from a Start Date onwards

  • Create and load the Start date into a date-type cube.
  • Apply the absolute offset to shift the date into the time dimension. Save the result in the Start Date flag cube.
  • To flag all the periods from the start date onwards it’s enough to apply the time function Last value.

4. Conclusion

The approach presented above is one way to address the challenge of flagging period between two dates or from one starting date onwards. This is the preliminary step to then start allocation or calculation in the identified period.

Tagged:

Comments

Categories