how to use filtered data in beast mode?

So I think this is pretty simple, but I'm not able to find how. I want to make a field using data that I can only get after a filter is applied. Basically I want a percentage. Using a count(field) I can get the total number of widgets, but I want to know how many of those widgets are purple, but I can only find out the number of purple widgets after I apply the filter to widgets.

so I'm looking for something like count(widgets - purple)/count(widgets) but I don't know how to actually enter the filtered results.

Thanks in advance

Best Answers

  • ColemenWilson
    edited May 28 Answer ✓

    Assuming your Widgets field is numeric:

    SUM(CASE WHEN `WidgetColor` = 'Purple' THEN `Widgets` ELSE 0 END) / SUM(`Widgets`)

    If I solved your problem, please select "yes" above

  • Data_Devon
    Data_Devon Contributor
    Answer ✓

    In my experience, the CASE WHEN statement has been the single most useful argument I've used for BeastModes. To solve your question, I'd do something like this:

    COUNT(CASE WHEN `Widgets` = 'Purple' THEN 1 ELSE 0)

    — the CASE WHEN argument looks at your `Widgets` column and returns a 1 if it's purple. You then count all these 1s to get the numerator of your solution

    /

    — Then you divide

    COUNT 'Widgets'END

    — by the total count of widgets.

    This should get you close - keep us posted on progress.

    ✅Did this solve your problem? Accept it as a solution!

    ❤️Did you love this answer? Mark it as "Awesome"!

    👍Do you agree with this process? Click "Agree"!

Answers

  • ColemenWilson
    edited May 28 Answer ✓

    Assuming your Widgets field is numeric:

    SUM(CASE WHEN `WidgetColor` = 'Purple' THEN `Widgets` ELSE 0 END) / SUM(`Widgets`)

    If I solved your problem, please select "yes" above

  • Data_Devon
    Data_Devon Contributor
    Answer ✓

    In my experience, the CASE WHEN statement has been the single most useful argument I've used for BeastModes. To solve your question, I'd do something like this:

    COUNT(CASE WHEN `Widgets` = 'Purple' THEN 1 ELSE 0)

    — the CASE WHEN argument looks at your `Widgets` column and returns a 1 if it's purple. You then count all these 1s to get the numerator of your solution

    /

    — Then you divide

    COUNT 'Widgets'END

    — by the total count of widgets.

    This should get you close - keep us posted on progress.

    ✅Did this solve your problem? Accept it as a solution!

    ❤️Did you love this answer? Mark it as "Awesome"!

    👍Do you agree with this process? Click "Agree"!

  • Data_Devon
    Data_Devon Contributor
    SpiderManGIF.gif

    @ColemenWilson is 🐐🐐🐐 so either of these solutions should work for you John_LARM

    ✅Did this solve your problem? Accept it as a solution!

    ❤️Did you love this answer? Mark it as "Awesome"!

    👍Do you agree with this process? Click "Agree"!

  • John_LARM
    John_LARM Member

    Thanks