Period to date sales beast mode

BrentJensen

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!

GrantSmith

@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)

 

GrantSmith

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.

BrentJensen

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.

???????

GrantSmith

@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)

 

jaeW_at_Onyx

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)

BrentJensen

@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
Sun	Mon	Tue	Wed	Thu	Fri	Sat
December 2020					January 2021
27	28	29	30	31	1	2

 

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

 

@GrantSmith , any comment?

 

Thanks again!

 

GrantSmith

@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

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:

BrentJensen

@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:

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

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

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

BrentJensen

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
27	28	29	30	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
November
25	26	27	28	29	30	31
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21

 

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 Name	Data Type	Data Sample
SALE_DATE	Date	Oct 29, 2020
REF_FDFY	Date	Dec 29, 2019
TY_FDFY	Date	Dec 27, 2020
LY_FDFY	Date	Dec 26, 2021
LLY_FDFY	Date	Jan 1, 2023
FISCAL_YEAR	Number	2020
FISCAL_PERIOD	Number	11
FISCAL_QUARTER	Number	4
FISCAL_WEEK	Number	44
FISCAL_DAY_IN_YEAR	Number	306
FISCAL_DAY_IN_WEEK	Number	5
STORE_NAME	Alpha	Springfield
STORE_NUMBER	Number	1
BANNER	Alpha	BPS

 

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

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.

 

nicangelica

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?