Unable to Create Same Time vs Yr Ago

MarshAm

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

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

marcel_luthi

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.

marcel_luthi

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

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

MarshAm

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

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

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.