Bucketing

I am trying to get bucketing for a certain data

the bucket is

250+ employees (count of female) (count of male) (their total salaries)

300+ employees (count of female) (count of male) (their total salaries)

350+ employees (count of female) (count of male) (their total salaries)

But when i try in beast mode

CASE WHEN `employee` > 250000
then '250k'
WHEN `employee` > 300000
then '300k'
WHEN `employee` > 350000
then '350k'
ELSE 'OTHER'
END

it gives the only 250k

Find more posts tagged with

Comments

GrantSmith

Hi @user056243

This is because the order in which your conditions are being evaluated. If you had 400,000 it compares to the first expression (>250) which is true so it returns '250k'. When doing a beast mode like this you need to start largest and go in descending order.

CASE WHEN `employee` >= 350000
then '350k'
WHEN `employee` >= 300000
then '300k'
WHEN `employee` >= 250000
then '250k'
ELSE 'OTHER'
END

I also updated your logic to be >= instead of > so that if you happened to have exactly 250,000 it'd fall in the correct bucket.

user056243

When I do as you suggested

350k shows 200

300k shows 110

250k shows 95

Ideally 250k should shows 95+110+200

300k should show 110+200

GrantSmith

A single beast mode won't work for how you're wanting to process your data. You're wanting to include a single record into multiple buckets which isn't possible as a single value is returned for each row from a beast mode. You could try and create separate beast modes for each bucket.