Unable to Create Same Time vs Yr Ago

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

Best Answer

  • marcel_luthi
    marcel_luthi 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

  • marcel_luthi
    marcel_luthi Coach

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

    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

    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.

  • GrantSmith
    GrantSmith 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:

    https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarshAm
    MarshAm Member
    edited March 2024

    https://community-forums.domo.com/main/discussion/comment/92666#Comment_92666

    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

  • MarshAm
    MarshAm Member
    https://community-forums.domo.com/main/discussion/comment/92668#Comment_92668

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

  • marcel_luthi
    marcel_luthi 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.

Categories