Beast Mode Calc - calculation in rows

billylobo

Hello,

Am I able to do a calculation like this in Beast Mode? From reading through, it seems like the formulas in Beast Mode are column oriented, but can I do a calculation to create new rows within the sheet itself?

Thanks for the help, Community!

Dome beast mode example.xlsx

ColemenWilson

USA Beast Mode:
(CASE WHEN `Country` = 'USA' AND Fruit = 'Oranges' THEN `Picked` ELSE 0 END +
CASE WHEN `Country` = 'USA' AND Fruit = 'Apples' THEN `Picked` ELSE 0 END) /
CASE WHEN `Country` = 'USA' AND Fruit = 'Pineapples' THEN `Picked` ELSE 0 END

Mexico Beast Mode:
(CASE WHEN `Country` = 'Mexico' AND Fruit = 'Oranges' THEN `Picked` ELSE 0 END +
CASE WHEN `Country` = 'Mexico' AND Fruit = 'Apples' THEN `Picked` ELSE 0 END) /
CASE WHEN `Country` = 'Mexico' AND Fruit = 'Pineapples' THEN `Picked` ELSE 0 END

DavidChurchman

The BeastMode could be written without the Country:

(sum(CASE WHEN Fruit = 'Oranges' THEN Picked ELSE 0 END) +
sum(CASE WHEN Fruit = 'Apples' THEN Picked ELSE 0 END) )/
sum(CASE WHEN Fruit = 'Pineapples' THEN Picked ELSE 0 END)

And the way you would "pivot" by country is by using country as a variable:

You could literally use a pivot table, too, but basically all the Domo cards work on the same logic as a pivot table, where the variable are saying how to split up the data.

ColemenWilson

USA Beast Mode:
(CASE WHEN `Country` = 'USA' AND Fruit = 'Oranges' THEN `Picked` ELSE 0 END +
CASE WHEN `Country` = 'USA' AND Fruit = 'Apples' THEN `Picked` ELSE 0 END) /
CASE WHEN `Country` = 'USA' AND Fruit = 'Pineapples' THEN `Picked` ELSE 0 END

Mexico Beast Mode:
(CASE WHEN `Country` = 'Mexico' AND Fruit = 'Oranges' THEN `Picked` ELSE 0 END +
CASE WHEN `Country` = 'Mexico' AND Fruit = 'Apples' THEN `Picked` ELSE 0 END) /
CASE WHEN `Country` = 'Mexico' AND Fruit = 'Pineapples' THEN `Picked` ELSE 0 END

billylobo

Thanks @ColemenWilson for the reply. This works. If I have many more countries, would I need a beast mode for each one like this?

The analogous situation in Excel is using a Calculated Item instead of a Calculated Field in a PivotTable.

DavidChurchman

The BeastMode could be written without the Country:

(sum(CASE WHEN Fruit = 'Oranges' THEN Picked ELSE 0 END) +
sum(CASE WHEN Fruit = 'Apples' THEN Picked ELSE 0 END) )/
sum(CASE WHEN Fruit = 'Pineapples' THEN Picked ELSE 0 END)

And the way you would "pivot" by country is by using country as a variable:

You could literally use a pivot table, too, but basically all the Domo cards work on the same logic as a pivot table, where the variable are saying how to split up the data.