Struggling to write Beastmode that reflects current MTD values

Golfoholic
Golfoholic Member
in Charting

I have a column of marketing sources where I am trying to breakout individual sources to be their own individual columns, and have them only reflect current MTD amounts. I have multiple YOY and MOM % and net differences in columns that interact with that date setting portion, so changing the date setting is going to change those Period Over Period columns. I have started by writing the following calc, but I'm unsure how to write and call out those specific values in the marketing source column.

CASE WHEN ((year(`Date_Entered`)=year(curdate())) AND (month(`Date_Entered`)=month(CURDATE()))) THEN `marketing_source` END

Marketing Source is the column that contains fields like Google, Google paid that I am trying to pull out in to their own columns

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

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

    https://www.youtube.com/watch?v=CDKNOmKClms&t=239s


    Either way. Good luck!

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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!
    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Golfoholic
    Golfoholic Member

    @jaeW_at_Onyx Ok now I can see. I am on the right path. So this data is meant to be in a table so that why there were custom calculated column for YOY and MOM and now the MTD amounts. sorry if I missed something but where is 'amount' coming from?

  • GrantSmith
    GrantSmith Coach

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Golfoholic
    Golfoholic Member
    Source Table Snippet.PNG
    Source Table Sample Data.xlsx

    @GrantSmith @jaeW_at_Onyx I attached what the table currently looks like. Showing the calculated columns for YOY and MOM values both the % and the net difference, along with the table date setting reflecting the past 13 months, which is what is interacting with the 2 period over period calculated columns. We are really just dealing with 1 column of data and comparing it to YOY and MOM, and reflect the MTD amounts for those 3 channels. I currently put the marketing_source_c column in and then just filtered to those 3 channels, but unfortunately they need to be broken out in to their own columns, and the current inquiries column is to reflect the total of those 3.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

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

    https://www.youtube.com/watch?v=CDKNOmKClms&t=239s


    Either way. Good luck!

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories