Beast Mode calculation

zuchu · June 2023

Hello,

I'm creating a visualization that aims to show orders that have a specific piece of equipment in it. My database has multiple rows of 'SO #', 1 row for a different type of equipment ordered. I want to see how many orders out the total have number '100' in column 'Equipment #'. In other words, for each 'SO #' I want to mark it as a 1 if it includes '100' in column 'Equipment #' and 0 if it doesn't include '100'.

Thanks!!

ColemenWilson · June 2023

I would do it in a dataflow. In Magic you would use the formula tile to get the flag on rows with a 100 in them and then use the group by tile to group on SO # and then join on SO # back to your granular data. Then you'd have a flag for each SO # that has an order product in it with 100 as shown in my last screenshot above. See below and let me know if you need more details:

Screen Shot 2023-06-26 at 1.32.44 PM.png

ColemenWilson · June 2023

Hi zuchu, see below for the beastmode to use:

CASE WHEN 'SO #' LIKE '%100%' THEN 1 ELSE 0 END

Review your data carefully because this would also pull in anything like '100' will be marked as 1. So, for example, 1001 would be counted even though it is not 100. If your order numbers don't have anything before or after the 100 you can remove one or both of the '%' signs.

zuchu · June 2023

@colemenwilson sorry if I caused some misunderstanding. Correct me if I'm wrong but but creating that case statement I'm looking at SO# that are like 100. I want to look at every single quote as a separate case, then look at 'Equipment #' (that's another column) and if for any 'SO #' there is an entry stating that 'Equipment #' is '100' then classify that order as 1, otherwise if the order didn't have 'Equipment #' 100, just leave it 0. Does that makes sense now??

MarkSnodgrass · June 2023

Sounds like you just need to look at the Equipment # column for your beast mode, which evaluates by row automatically. It would look like this:

CASE WHEN 'Equipment #' LIKE '%100%' THEN 1 ELSE 0 END

ColemenWilson · June 2023

Okay I think I see what you are saying. You want an output like this right:

Screen Shot 2023-06-26 at 1.17.11 PM.png

zuchu · June 2023

@colemenwilson thats exactly what my dataset looks like!! Once I have the the flagged version, I'll use distinct SO # and will have my answer :) Do you know how to make that calculation?

ColemenWilson · June 2023

I would do it in a dataflow. In Magic you would use the formula tile to get the flag on rows with a 100 in them and then use the group by tile to group on SO # and then join on SO # back to your granular data. Then you'd have a flag for each SO # that has an order product in it with 100 as shown in my last screenshot above. See below and let me know if you need more details:

Beast Mode calculation

Best Answer

Answers

Categories