Using Variables in Beast Mode

kevin_johnson

Hi all,

I'm trying to write a Beast Mode and it would be much easier if I could declare a variable to make my code a bit simpler. I would want to do something like this:

CASE 

WHEN `Date of Death` is NULL THEN FLOOR(DATEDIFF(CURRENT_DATE(), `Date of Birth`)/365)

ELSE FLOOR(DATEDIFF(`Date of Death`, `Date of Birth`)/365)

END AS AGE,

CASE 

WHEN AGE < 1 THEN '>1'

WHEN AGE BETWEEN 1 AND 3 THEN '1-3'

WHEN AGE BETWEEN 4 AND 6 THEN '3-6'

WHEN AGE BETWEEN 7 AND 9 THEN '7-9'

WHEN AGE BETWEEN 10 AND 12 THEN '10-12'

WHEN AGE BETWEEN 13 AND 15 THEN '13-15'

WHEN AGE BETWEEN 16 AND 18 THEN '16-18'

ELSE '18+'

END

But this code isn't running for me.

So I was wondering if the issue is my declaring the variable or if there's some other issue with the code.

Thanks in advance!

MarkSnodgrass

Yes, you can't create a "variable" in a beast mode and try and reference it later in your beast mode. Domo is working on functionality to allow you to nest beast modes, so you could create a beast mode called age and then in your age range beast mode you could reference the age beast mode.

Your workaround right now would be to calculate the age in an ETL so that it is a field in your dataset, or rework your case statement to do your age calculation in each when clause.

MarkSnodgrass

Yes, you can't create a "variable" in a beast mode and try and reference it later in your beast mode. Domo is working on functionality to allow you to nest beast modes, so you could create a beast mode called age and then in your age range beast mode you could reference the age beast mode.

Your workaround right now would be to calculate the age in an ETL so that it is a field in your dataset, or rework your case statement to do your age calculation in each when clause.

MarkSnodgrass

Here's a re-worked case statement:

CASE

WHEN Date of Death is NULL THEN FLOOR(DATEDIFF(CURRENT_DATE(), Date of Birth)/365)

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 1 THEN '<1'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 3 THEN '1-3'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 6 THEN '3-6'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 9 THEN '7-9'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 12 THEN '10-12'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 15 THEN '13-15'

WHEN FLOOR(DATEDIFF(Date of Death, Date of Birth)/365) < 18 THEN '16-18'

ELSE '18+'

END

Due to how case statement functions, you are able to remove the between logic and just use less than logic which makes the statement a bit more concise.

Hope this helps.