Beast mode calculation

domoexpert

I want to create a beast mode calculation which should be something like this

Value for current month this year - value for current month last year / Value for current month last year

How can i do this?

MichelleH

@domoexpert You can accomplish this using a case statement within an aggregate for each value in your calculation. It should look something like this:

(sum(case when LAST_DAY(`DateField`) = LAST_DAY(CURRENT_DATE()) then `ValueField` end) - 
sum(case when LAST_DAY(`DateField`) = LAST_DAY(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `ValueField` end))
/ 
sum(case when LAST_DAY(`DateField`) = LAST_DAY(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `ValueField` end)

domoexpert

Is there a way to do it other than current date since the data is lagged and might not be up to current date. Any way to do it for max month thats available

MichelleH

@domoexpert Yes, you could do that. In that case I'd recommend creating a "Max Date" column in your dataset using the Group by ETL tile to use instead of CURRENT_DATE()

domoexpert

Can that not be done in beast mode?

MichelleH

@domoexpert You can try using MAX(`DateField`) instead of CURRENT_DATE() in the beast mode, though it's possible the Max Date could change if you apply filters to the card. I have also found that using an aggregate (MAX) inside another aggregate (SUM) is less reliable in beast mode

domoexpert

((CASE WHEN YEAR(`Date`) = MAX(YEAR(`Date`)) and MONTH(`Date`) = MAX(MONTH(`Date`)) THEN SUM(`Value`) END) -

(CASE WHEN YEAR(`Date`) = MAX(YEAR(`Date`))-1 and MONTH(`Date`) = MAX(MONTH(`Date`)) THEN SUM(`Value`) END))
/
(CASE WHEN YEAR(`Date`) = MAX(YEAR(`Date`))-1 and MONTH(`Date`) = MAX(MONTH(`Date`)) THEN SUM(`Value`) END)

I am doing this but this is giving me an error

@MichelleH

marcel_luthi

What kind of card are you trying to use and what does the raw data look like? Some cards in Domo come pre-built with Period over Period capabilities that would take care of showing this for you without the need of any BeastMode, this works great specially if your data for each month is calculated by the default Group By configuration of the Date Range.

Depending on your needs you might want to do a view first that summarizes things at a monthly level and use a lag function and use a lag function and filters on your beast mode to get what you want.

ST_Superman

@domoexpert

What type of visualization are you using? You can accomplish this with the Period over Period "Variance bar line" chart type:

domoexpert

I am trying to build a simple table calculation.

ST_Superman

If you used a variable to set the current month and comparison month, then you could write a beastmode to get this calculation. But then the user would need to set those values when they view the card. (you can select a default value, but unfortunately the default can't be "current month". You would need to update the default selections each month)

domoexpert

The real question is that why isnt the above calculation working?