How can I flag all periods between 2 dates?

The Hackett Group
The Hackett Group Active Partner
Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board Name Dropper First Comment
in Platform

Hi,

I need to load some discount values from a table in SQL but the table does not have a month column. Instead, we have 2 separate columns; for Start Date and End Date.

In normal circumstances, I would just take the last value from each of these dates and subtract the two lists, leaving me with the period between being flagged, but this is not possible in this dataset, as we will have multiple start and end dates for a given product.

Below is an example of the result that I need to achieve:

Untitled Image

Has anyone dealt with a similar problem before? I have a workaround that I can perform in SQL, but I'm sure there must be a better solution.

Thanks!

Tagged:

Accepted Answer

  • Federica Antonelli
    Federica Antonelli Employee
    Fourth Anniversary 25 Likes 10 Comments Level 300: Expanding Board in Your Organization
    Answer ✓

    Hello @The Hackett Group - another approach is the following:

    • Load the start and the end date into two cubes (cube type: date) - you need to add all the dimensions in the cube to be able to add all the start and end dates for each product if you have multiple dates for each. (f.e if you have numerous promo for each product you need the entity promotion);
    • Use the dataflow time offset (absolute) - to shift Cube the values (dates) on the time dimension;

    In this way you will have the first two columns of the table you presented above.

    • To get the result in column three, create the following procedure:
      • Dataflow 1:
        • Temp cube (C) = Cube in column 1 - Cube in column 2 (with Offset -1) applied → This is because you need to include in your range also the end date.
      • Dataflow 2
        • target cube = 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.

    Hope this helps

    Federica

Answers

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    250 Likes Fourth Anniversary 100 Up Votes 100 Comments
    edited October 2

    Hi @The Hackett Group ,

    first of all, you'll need to calculate the number of days between start- and end-date to be used as time offset. Then you set the start date in the output cube to 1 and build a dataflow with 3 blocks. a and c will be your output cube, b will be the time offset cube. The algorithm for c=if(a=1,a*2,0) .

    Set the dynamic offset in the flow's configuration to relative forward using the time offset as driver (that's why this cube has to be part of the flow's layout).

    image.png

    That will flow a "2" to the end date.

    The next step in your procedure is a dataflow with twice the output cube (a and b), a set to "Last Value". The algorithm b=a.

    Then a dataflow again with twice the output cube b=if(a=1,1,0) to get rid of the "2".

    That should do the trick.

    If you know the end-date you could set it directly to "2" in the output cube and don't need to consider the time-offset flow. Skip directly to b=LastValue(a).

    Best,
    Helmut

  • The Hackett Group
    The Hackett Group Active Partner
    Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board Name Dropper First Comment
    https://community.board.com/discussion/comment/18646#Comment_18646

    Thanks all, I ended up using a modified version of this - I didn't need the date cubes because I had flags already populated.

    A very quick and tidy solution which has helped me replace a slow and cumbersome SQL process! :)

Categories