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

verytiredgirl · 2024-09-12T21:37:21+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Cards

Beast Mode

Accepted answers

Manasi_Panov

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

All comments

ColemenWilson

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.

Manasi_Panov

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:

or Progress Bar:

Here is the sample table I'm using:

verytiredgirl

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

Manasi_Panov

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?

verytiredgirl

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

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