Beastmode to return current month value? And what's the best card type to show the variance?

verytiredgirl
verytiredgirl Member
edited September 12 in Beast Mode

My dataset has these columns: year (int), month (int), target, actual
I created a BeastMode to return the current month (Aug) value for both target & actual, but instead of showing an actual value for Aug, it shows 0.

CASE
WHEN month = MONTH(CURRENT_DATE()) THEN target
ELSE '0'
END

Since I want to see the variance for current month of both target & actual, what would be the best card to do that? Just the number value, I have already have a bar showing the variance for all months

Tagged:

Best Answer

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @verytiredgirl,

    I'm glad I could help. You can simply adjust the Beast mode subtracting 1 from the CURRENT_DATE() or use the SUBDATE() function:

    CASE

    WHEN Month = MONTH(CURRENT_DATE()) - 1 AND Year = YEAR(CURRENT_DATE()) THEN Actual

    ELSE 0

    END

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • ColemenWilson
    ColemenWilson Coach

    Can you share a screenshot of your analyzer view? For your second question, I would a multi-value gauge card. I like using these to show a single variance between two values.

    Your beastmode should work, you need to include year in your beastmode logic as well or else values from other years would be included.

    If I solved your problem, please select "yes" above

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @verytiredgirl,

    There are no issues visualizing the Current Month Target and Actual. If you have data spanning multiple years, please include the year in the Beast Mode formula. Examples are provided for reference:


    CASE
    WHEN Month = MONTH(CURRENT_DATE()) AND Year = YEAR(CURRENT_DATE()) THEN Actual
    ELSE 0
    END

    and

    CASE
    WHEN Month = MONTH(CURRENT_DATE()) AND Year = YEAR(CURRENT_DATE()) THEN Target
    ELSE 0
    END

    Then you can visualize with Filled Gauge chart:

    image.png

    or Progress Bar:

    image.png

    Here is the sample table I'm using:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • verytiredgirl
    verytiredgirl Member

    Hi I added year in my BeastMode, but the current value still show '0.00'

    image.png

  • Manasi_Panov
    Manasi_Panov Contributor

    Hi @verytiredgirl,

    The current month is 9. From what I can see from the table, there is no data for the month of September. Could you please check?

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • verytiredgirl
    verytiredgirl Member

    Thanks for catching that! I forgot it's an end of month kind of report, I need to update my Beast Mode to reflect last month then, thank you!

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @verytiredgirl,

    I'm glad I could help. You can simply adjust the Beast mode subtracting 1 from the CURRENT_DATE() or use the SUBDATE() function:

    CASE

    WHEN Month = MONTH(CURRENT_DATE()) - 1 AND Year = YEAR(CURRENT_DATE()) THEN Actual

    ELSE 0

    END

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories