Skip to content

    • Beast Mode

    Beast Mode

    Unable to Create Same Time vs Yr Ago

    Member
    in Beast Mode

    I'm trying to create a dynamic Comparison Value that adjusts the view based on Time Period Selection. I was able to create a Gauge Value that works, but am struggle with the Beast Mode that would allow the end user to view vs same time year ago value.

    I have the code below and have tried various adjustments, but keep getting a syntax error from Domo (which isn't helpful at all).

    image.png

    CASE
        WHEN Time Period Selection: = '4' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
        WHEN Time Period Selection: = '13' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 13 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
        WHEN Time Period Selection: = '26' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 26 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
        WHEN Time Period Selection: = '52' AND WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 52 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
        WHEN Time Period Selection: = 'YTD' AND YEAR(WEEK_STARTING) = YEAR(CURRENT_DATE()) - 1 AND WEEK_STARTING BETWEEN DATE_TRUNC('YEAR', CURRENT_DATE() - INTERVAL 1 YEAR) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) THEN SELL_OUT_VALUE
        ELSE 0
    END

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Coach
      Answer ✓

      Just to be clear, what I posted is not the full Beast Mode, just what I suggested to replace the BETWEEN condition inside of each of the WHEN statements and not meant to be entered on it's own.

      I'd guess you're using the Multi-Value Columns Chart Type, if so what does your source dataset looks like, for this to work you'll need to aggregate your data accordingly, most likely using the FIXED function since you'll need to include the full dataset and then limit what gets counted for on each case, but without knowing what your Dataset looks like it's hard to make any suggestions.

    Answers

    • Coach

      I try not to use BETWEEN as I seem to never get it right, try replacing BETWEEN with two comparisons something like:

      1. WEEK_STARTING BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)

      Replaced by

      1. WEEK_STARTING >= DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 4 WEEK) AND WEEK_STARTING <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)

      Perhaps that will help solving the syntax error you're getting.

    • Coach

      Typically when I'm doing period over period analysis I'll restructure my data to make things like this easier. I've outlined how to do this here:

      A more flexible way to do Period over Period comparisons

      GrantSmith
      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member
      edited March 2024

      marcel_luthi

      This also gave a syntax error. Is this something Domo can do? I'm relatively new to the platform so not sure of the limitations

    • Member

      This would be great if possible but it's not something we can do.

    • Coach
      Answer ✓

      Just to be clear, what I posted is not the full Beast Mode, just what I suggested to replace the BETWEEN condition inside of each of the WHEN statements and not meant to be entered on it's own.

      I'd guess you're using the Multi-Value Columns Chart Type, if so what does your source dataset looks like, for this to work you'll need to aggregate your data accordingly, most likely using the FIXED function since you'll need to include the full dataset and then limit what gets counted for on each case, but without knowing what your Dataset looks like it's hard to make any suggestions.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories