Use loops to avoid rewriting case statements within a beast mode many times

user060355 · 2025-07-08T12:34:39+00:00

There was an error rendering this rich post.

I find myself writing bucketing beast modes that are very manual, tedious, and often not accurate. I was wondering if there's a way to accomplish something like this using a variable or something so I:

1) Wouldn't need to retype the same thing over and over
2) Could modify my bucket size all at once

Here's an example:

Find more posts tagged with

Beast Mode

Accepted answers

GrantSmith

Since your buckets are the same size you can use some math and concat to calculate your buckets for you: You can take care of the end buckets first otherwise calculate the bucket name dynamically.

CASE WHEN `maint_count_between_restarts` = 0 THEN 0
WHEN `maint_count_between_restarts` > 50 THEN 50
ELSE CONCAT(FLOOR(`maint_count_between_restarts`/5)*5+1, '-', CEILING(`maint_count_between_restarts`/5)*5)
END

This will allow you to more easily calculate the buckets. In this example the bucket is 5 but you can change the 5 to whatever size bucket you want, just makre sure it's updated in all 4 locations.

You can create a separate beast mode for sorting:

CASE WHEN `maint_count_between_restarts` = 0 THEN 0
WHEN `maint_count_between_restarts` > 50 THEN 50
ELSE FLOOR(`maint_count_between_restarts`/5)*5+1
END

All comments

GrantSmith

Since your buckets are the same size you can use some math and concat to calculate your buckets for you: You can take care of the end buckets first otherwise calculate the bucket name dynamically.

CASE WHEN `maint_count_between_restarts` = 0 THEN 0
WHEN `maint_count_between_restarts` > 50 THEN 50
ELSE CONCAT(FLOOR(`maint_count_between_restarts`/5)*5+1, '-', CEILING(`maint_count_between_restarts`/5)*5)
END

This will allow you to more easily calculate the buckets. In this example the bucket is 5 but you can change the 5 to whatever size bucket you want, just makre sure it's updated in all 4 locations.

You can create a separate beast mode for sorting:

CASE WHEN `maint_count_between_restarts` = 0 THEN 0
WHEN `maint_count_between_restarts` > 50 THEN 50
ELSE FLOOR(`maint_count_between_restarts`/5)*5+1
END

DavidChurchman

OP asked if you could use a variable, so to add to @GrantSmith's answer, you could replace every 5 in his answer with a variable (let's say it has a default value of 5) and then dynamically adjust the bucket size on your dashboard with a variable control.

DavidChurchman

Also, in MagicETL, you could use a scripting tile to use R or Python to do this with a for-loop, but if you go that route, it would probably be easier to use a built in function like cut() from R or pandas.cut().

user060355

https://community-forums.domo.com/main/discussion/comment/102250#Comment_102250

I think there is an issue with this. As you can see on the x-axis, the groupings are not what you'd expect. 1-5 and 6-10 look good, but the next value is 6-5 followed by 11-10 and this pattern continues. Can you please correct the equation?

GrantSmith

https://community-forums.domo.com/main/discussion/comment/102265#Comment_102265

There's an edge case which is causing the wrong labels. Try the following:

CASE WHEN `maint_count_between_restarts` = 0 THEN 0
WHEN `maint_count_between_restarts` > 50 THEN 50
ELSE CONCAT(FLOOR(`maint_count_between_restarts`/5)*5+1, '-', FLOOR((`maint_count_between_restarts`+5)/5)*5)
END