Trying to hide past months for one value in a beastmode

gbrown
gbrown Member
in Charting

I am trying to use our actuals to track past month/current value and our forecast to use future month value and I am having trouble writing the beat mode to do this.

CASE WHEN YEAR(`Period Date`) <= YEAR(CURDATE()) AND MONTH(`Period Date`) <= MONTH(CURDATE()) 

THEN 'Hide Future Months'

ELSE 'Show Future Months'

END


Here is the SQL to hide future months for all values in the card but is there a way to hide past months for one particular value? the value I am trying to hide for past months is just named 'value' in the dataset.


Thanks

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    You can use a case statement to conditionally return a value or NULL if it doesn't match the logical expression.


    Something like:

    CASE WHEN LAST_DAY(`Period Date`) <= LAST_DAY(CURDATE()) THEN `Value` END

    You can then use this beast mode in place of your original value.

    I'm using LAST_DAY to return the last day of the month to simplify checking for future dates instead of having to check for the year and the month separately. With your current logic it appears exclude December of last year as 2021 is <= 2022 and 8 <= 12. Typically going this route you'd need to check for the year being before this year OR (the years are the same AND the month comes before this month) to include, or alternatively just use LAST_DAY.

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

Categories