Best Of

@AJ2020 is this a duplicate post? or did my lengthy response not come through (sorry i thought i answered this last week).

EDIT:: totally misread your requirement.

Hi @AJ2020

MOD is the modulo operation. It simply says "divide these two numbers together and give me the remainder". In other words, how many left over days do I have after calculating the number of full weeks.

FLOOR just returns the whole number of a division operation without the fraction. I'm using it in two places. The first FLOOR([DAYS]/5) is telling me the number of whole "business" weeks I'm calculating to multiply by 7 to convert them to actual weeks. The other is just telling me if the start day is a Saturday (7) and if so subtract 1 for a one day weekend instead of 2.

It needed to add the number of business days remaining. My apologies. Currently it was only ever adding a single business day (+ 1) and not the day making up the partial week.

-- https://dojo.domo.com/discussion/52342/business-days-in-date-add-interval#latest
-- Logic:
-- Calculate the number of full business weeks
-- Convert to actual 7 day work weeks
-- Determine if adding the remining business days will go over a weekend
-- if so, add 2 (or 1 if starting on a Saturday) to "jump" the weekend.
-- Add the remaining business days.

-- New after 3 PM
CASE WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(2/5) * 7 + CASE WHEN MOD(2,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(2,5)) DAY))

-- New before 3 PM
WHEN (`PurposeType`= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(1/5) * 7 + CASE WHEN MOD(1,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(1,5)) DAY))

-- Renew after 3 PM
  WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)>=15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL (FLOOR(4/5) * 7 + CASE WHEN MOD(4,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END + MOD(4,5)) DAY))

-- Renew before 3 PM
WHEN (`PurposeType`!= 'New') AND (HOUR(`StartDateTime`)<15) THEN DATE(DATE_ADD(`StartDateTime`, INTERVAL
-- Number of Full Weeks that make up the number of business days -> convert to actual 7 day weeks
(FLOOR(3/5) * 7 +
-- MOD tells us the remainder of business days which don't make up a full business week
-- Does this go over a weekend?
 -- if so, add 2 (or 1 if starting on a Saturday) to "jump" the weekend.
CASE WHEN MOD(3,5) + DAYOFWEEK(`StartDateTime`) >= 7 THEN 2 - FLOOR(DAYOFWEEK(`StartDateTime`)/7) ELSE 0 END
-- Add the remaining business days
 + MOD(3,5)) DAY))

ELSE ' '

END

Google:

CASE WHEN `marketing_source_c` = 'Google' THEN `Date_entered` END

Google Paid:

CASE WHEN `marketing_source_c` = 'Google Paid' THEN `Date_entered` END

SPS_Website:

CASE WHEN `marketing_source_c` = 'SPS_Website' THEN `Date_entered` END

Essentially you'd have those three case statements in 3 separate beast modes when you're calculating anything dealing with your Date_entered column instead of just plain `Date_entered`.

I'm not certain of your exact beast modes you're using to calculate the YOY or MOM numbers but doing a YOY might look something like this: (this is all untested)

Google:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)

Google Paid:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google Paid' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)

SPS_Website:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'SPS_Website' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)

Hi @user048760

The amount would be the column in your table that contains the MTD amounts in your table. It's the column name.

What @jaeW_at_Onyx is suggesting is that you split out each source into its own beast mode. For example:

Google:

sum( case when marekting_source = 'Google' then `Current MTD Amount` end ) 

Google Paid:

sum( case when marekting_source = 'Google Paid' then `Current MTD Amount` end ) 

And so on. You'd need to make sure the sources are correct (I'm guessing since I don't have any sample data to go off of) and that the name of the column storing the value you wish to bucket is correct as well (again I'm assuming Current MTD Amount is the name of your column but it'll need to be changed to the name you have)

if you have different marketing sources blended into the same column then you need

sum( case when marekting_source = 'value' then `amount` end ) 

i would recommend that you not calculate YOY or MOM as separate columns in your dataset. it makes it difficult to apply filters.

instead just use either window functions in analyzer or beast modes like you've done.

sum(case when year = year(curdate()) and `marketing_source` = 'value' then amount end )

Good luck!

 @user048760 , @GrantSmith is spot on in the math. It will get the job done.

But this design / approach is ... difficult to scale because it requires you to never change the filters on your cards (notice how you currently have it set for 13 months. If a user wants to drill into a specific month or applied a date filter to the dashboard this card is hosted on, the card would 'break.'

This tutorial, is a bit of a stretch to wrap your mind around, but it's a much more scalable solution that uses an "offset date dimension" table design pattern that we consultants find ourselves using in Domo very frequently.

Either way. Good luck!

Hi @user048760

You'd need three separate beast modes, one for each source. You just need to include the condition to filter the appropriate source in your YOY, MOM etc calculations.

For example for Google:

(These are all untested code)

• Inquiries

COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)

• YoY

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)

• MoM

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)

For each of your different sources you'd need a different beast mode then you can drag those beast modes onto your card. Hopefully this helps explain things a bit better for you.

@HowDoIDomo , consider using a Bullet Chart if you don't want an actual line overlayed on a bar chart

There is a Period Over Period chart type to allow you to compare to the previous year of data.

I would be wary of the Regression b/c as soon as people start adding filters the results get misinterpreted (b/c it's limited to the filtered set of rows i.e. what's visible instead of the entire dataset.)