Period to date sales beast mode

Currently using this beast mode for "this year period to date sales":

 

SUM(CASE WHEN `SALE_DATE` > CURDATE()-40 AND

(CASE
WHEN CURDATE()-1 >= '12/30/2018' AND CURDATE()-1 <= '1/26/2019' THEN 1
WHEN CURDATE()-1 >= '1/27/2019' AND CURDATE()-1 <= '2/23/2019' THEN 2
WHEN CURDATE()-1 >= '2/24/2019' AND CURDATE()-1 <= '3/30/2019' THEN 3
WHEN CURDATE()-1 >= '3/31/2019' AND CURDATE()-1 <= '4/27/2019' THEN 4
WHEN CURDATE()-1 >= '4/28/2019' AND CURDATE()-1 <= '5/25/2019' THEN 5
WHEN CURDATE()-1 >= '5/26/2019' AND CURDATE()-1 <= '6/29/2019' THEN 6
WHEN CURDATE()-1 >= '6/30/2019' AND CURDATE()-1 <= '7/27/2019' THEN 7
WHEN CURDATE()-1 >= '7/28/2019' AND CURDATE()-1 <= '8/24/2019' THEN 8
WHEN CURDATE()-1 >= '8/25/2019' AND CURDATE()-1 <= '9/28/2019' THEN 9
WHEN CURDATE()-1 >= '9/29/2019' AND CURDATE()-1 <= '10/26/2019' THEN 10
WHEN CURDATE()-1 >= '10/27/2019' AND CURDATE()-1 <= '11/23/2019' THEN 11
WHEN CURDATE()-1 >= '11/24/2019' AND CURDATE()-1 <= '12/28/2019' THEN 12
WHEN CURDATE()-1 >= '12/29/2019' AND CURDATE()-1 <= '01/25/2020' THEN 1
WHEN CURDATE()-1 >= '01/26/2020' AND CURDATE()-1 <= '02/22/2020' THEN 2
WHEN CURDATE()-1 >= '02/23/2020' AND CURDATE()-1 <= '03/28/2020' THEN 3
WHEN CURDATE()-1 >= '03/29/2020' AND CURDATE()-1 <= '04/25/2020' THEN 4
WHEN CURDATE()-1 >= '04/26/2020' AND CURDATE()-1 <= '05/23/2020' THEN 5
WHEN CURDATE()-1 >= '05/24/2020' AND CURDATE()-1 <= '06/27/2020' THEN 6
WHEN CURDATE()-1 >= '06/28/2020' AND CURDATE()-1 <= '07/25/2020' THEN 7
WHEN CURDATE()-1 >= '07/26/2020' AND CURDATE()-1 <= '08/22/2020' THEN 8
WHEN CURDATE()-1 >= '08/23/2020' AND CURDATE()-1 <= '09/26/2020' THEN 9
WHEN CURDATE()-1 >= '09/27/2020' AND CURDATE()-1 <= '10/24/2020' THEN 10
WHEN CURDATE()-1 >= '10/25/2020' AND CURDATE()-1 <= '11/21/2020' THEN 11
WHEN CURDATE()-1 >= '11/22/2020' AND CURDATE()-1 <= '12/26/2020' THEN 12
END)

= `FISCAL_PERIOD` THEN `SALES` END)

 

The pertinent data is structured like this:

SALE DATE  |REF_FDFY   |TY_FDFY     |LY_FDFY  |FISCAL_YEAR|FISCAL_PERIOD|FISCAL_WEEK|FISCAL_DAY_IN_YEAR|FISCAL_DAY_IN_WEEK|

date field       |date field       |date field      |date field    | numeric field  | numeric field      | numeric field    | numeric field            | numeric   field                      |

May 10, 2020|Dec 29, 2019|Dec 27, 2020 |Dec 26, 2021| 2020           |  5                        |   20                 |    134                        |  1                                         |

 

Have tried a number of variations, can't find anything that will revise the beast mode without the need to input the fiscal period dates at the beginning of every year.

 

Any help is appreciated.

 

Thanks in advance!

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    Accepted Answer

    @user016095 

    `dt` is the name of the column that contains the date field.

     

    In your example you'd replace `dt` with 

    CURDATE()-1

     

    Resulting with:

     

    SUM(CASE WHEN `SALE_DATE` > CURDATE()-40 AND
    
    MONTH(DATE_ADD(CURDATE()-1, 7 - DAYOFWEEK(CURDATE())))
    
    = `FISCAL_PERIOD` THEN `SALES` END)

     

Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    Hi @user016095 

     

    It appears that your logic for fiscal period is determining the month of the last day in the week. You can replicate that logic fairly easily utilizing some date manipulation functions in a beast mode without worrying about the month.

     

    MONTH(DATE_ADD(`dt`, 7 - DAYOFWEEK(`dt`)))

     

    To break this down:

    7 - DAYOFWEEK(`dt`)

     DAYOFWEEK returns a number 1-7 for which day of the week it is. Subtracting that number from 7 tells us the number of days until the end of the week

     

    DATE_ADD(`dt`, 7 - DAYOFWEEK(`dt`))

    We add that value to the current date to get the actual date at the end of the week.

     

    MONTH(DATE_ADD(`dt`, 7 - DAYOFWEEK(`dt`)))

    Finally we take the month of the date at the end of the week..

     

    Putting a nice bow on all of this your beast mode would then simplify down to:

    SUM(CASE WHEN `SALE_DATE` > CURDATE()-40 AND
    
    MONTH(DATE_ADD(`dt`, 7 - DAYOFWEEK(`dt`)))
    
    = `FISCAL_PERIOD` THEN `SALES` END)

     

    This will then handle any future years without having to go through and update your beast modes.

  • To what is 'dt' referring?

    It looks like a reference to a data column in the dataset, but there isn't a column with that name.

    ???????

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    you really should consider implementing a date dimension that contains one row for every day since the dawn of time and calculates your fiscal calendar weeks etc.  to assist you could create a webform that indicates the start of each fiscal year and then just use SQL to derive the rest.

     

    The risk of embedding it in beast modes is that if you have two or three datasets, you now have to maintain dense code in two or three places.

     

    Just do it right once and recycle the date dimension (by JOINING it to your other datasets)

  • @GrantSmith 

    Only remaining concern is the 1st period of a fiscal year. This is often a fiscal month where there are days in different calendar years. This chart helps explain:

     

    2021 Fiscal week 1
    SunMonTueWedThuFriSat
    December 2020January 2021
    272829303112

     

    I'm still testing to see if this becomes a problem. 

     

    @GrantSmith , any comment?

     

    Thanks again!

     

  • GrantSmith
    GrantSmith Indiana 🥷

    @user016095 - This shouldn't be an issue with the Beast Mode. It's getting the last day of the week and then calculating which month that date is a part of.

     

    In your example if we had December 30th it would calculate the date of Jan 2nd 20201. Then it gets the month of 1/2 returning 1.

  • GrantSmith
    GrantSmith Indiana 🥷

    Utilizing the key part of the Beast Mode for Fiscal Period:

    MONTH(DATE_ADD(`dt`-1, 7 - DAYOFWEEK(`dt`)))

     

    Here's verification for you utilizing the Domo Dimensions - Calendar dataset:

    Screen Shot 2020-10-23 at 3.10.56 PM.png

  • @GrantSmith This was not an adequate solution. All of last week, the calendar month and fiscal month were in different months. I created a beast mode in our dataset using the code from this solution and set it up for a side by side comparison with the hard-coded beast mode:

    user016095_0-1604329176642.jpeg

    I think the part of the beast mode in the solution that refers to the calendar month is not going to work because of this situation where at the beginning of many months, there are days which are not in the same calendar and fiscal month. Still looking for a solution....

     

  • GrantSmith
    GrantSmith Indiana 🥷

    So for last week, what were the calendar and fiscal months you were expecting? 10 and 11 respectively?

     

    With the image you posted are you suggesting that the two numbers should line up?

     

    How are you grouping your data?

     

    Which columns represents the hard coded formula and which represents the new formula?

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Not to flog a dead horse, but the issue with years or months starting in the middle of weeks is literally the issue that dimension tables were designed to address.  If you build it once, you can eliminate all these dense and convoluted CASE statements.

     

    This is a recurring issue in the Dojo, so I built a tutorial video on creating a week-based date dimension table in MagicETL 2.0

     

    https://www.youtube.com/watch?v=UO9YUfkSh7I

  • GrantSmith 

    Expecting that in the screen capture of the card output above that the beast mode:

     

    SUM(CASE WHEN `SALE_DATE` > CURDATE()-40 AND

    MONTH(DATE_ADD(CURDATE()-1, 7 - DAYOFWEEK(CURDATE())))

    = `FISCAL_PERIOD` THEN `SALES` END)

     

    would be able to provide correct data for the first fiscal week of November even though that week lies in the last calendar week of October: 

     

    October
    27282930123
    45678910
    11121314151617
    18192021222324
    November
    25262728293031
    1234567
    891011121314
    15161718192021
     

    In the card image above, the gold shaded columns are the original report with the hard-coded beast modes. The white columns are those that were derived from the new beast mode. If the new beast mode provided a correct calculation, these numbers would be the same.

     

    The data is grouped so that every product sold at each store each day has its own row of data. The row gives the total dollar value of the product sold, by sku, with a count of the number of items sold along with dollar value, cost, margin.... etc.

     

    These are the columns that denote the date details:

     

    Column NameData TypeData Sample
    SALE_DATEDateOct 29, 2020
    REF_FDFYDateDec 29, 2019
    TY_FDFYDateDec 27, 2020
    LY_FDFYDateDec 26, 2021
    LLY_FDFYDateJan 1, 2023
    FISCAL_YEARNumber2020
    FISCAL_PERIODNumber11
    FISCAL_QUARTERNumber4
    FISCAL_WEEKNumber44
    FISCAL_DAY_IN_YEARNumber306
    FISCAL_DAY_IN_WEEKNumber5
    STORE_NAMEAlphaSpringfield
    STORE_NUMBERNumber1
    BANNERAlphaBPS

     

    Columns further out give store, product, and pricing details.

     

    In regards to the previous post about "...implementing a date dimension that contains one row for every day since the dawn of time and calculates your fiscal calendar weeks etc." I completely agree and we are aware of how to implement and the benefits. But since this is the largest dataset (NZ MASTER SALES V2) we have in our DOMO instance (65 columns  344,688,655 rows) it would be impractical to make such a change to the dataset.

     

    Let me know if any other details are needed to assist.

     

    Thanks again for trying!

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    a fusion on that many rows will work fine.  We've used this solution on billion-row datasets.

    It looks like your data has a lot of dimensional columns already. 

     

    Using a date dimension, you could compress the width of your fact table and build a framework that introduces a degree of flexibility

     

    with 65 columns of low cardinality data, you should be fine, but you can ask domo to "optimize the fusion" ... they can do backend stuff to optimize performance on relatively stable schemas.

     

    they'll push you to reduce the number of columns in your dataset to optimize query performance but you should do that regardless of whether you're using a fusion or not.

     

  • Hi @jaeW_at_Onyx, I saw your tutorial video on this and I'm interested with the 'OneToMany' dataflow table that you have and I think I might have a lot of use for that. May I know how you did it?