As a data analyst, one of the most common tasks I found myself performing was bucketing values into sensible bins. Think of a column of client ages with values ranging from 15 years old to 76, where you would be wanting to uncover any insights or trends across age groups. In this scenario, the first thing you’d want to do is to distribute these age values into buckets of say, 20 years, then assign names to these buckets.
This may look like the following:
- less than 17 years is Child
- more than 17 but less than/equal to 25 is Youth,
- more than 25 but less than/equal to 35 is Adult,
- above 35 is Mature
This calls for multiple IF statements. Whilst the concept remains similar, the syntax varies across platforms.
1. Tableau (Calculated Field)
IF [Age] <= 17 THEN “Child”
ELSIF [Age] <= 25 THEN “Youth”
ELSIF [Age] <= 35 THEN “Adult”
ELSE “Mature”
END
2. Power BI (DAX in a calculated column)
Age Bracket =
SWITCH(
TRUE(),
‘TableName'[Age] <= 17, “Child”,
‘TableName'[Age] <= 25, “Youth”,
‘TableName'[Age] <= 35, “Adult”,
“Mature”
)
3. Alteryx (Formula tool)
IF [Age] <= 17 THEN “Child”
ELSIF [Age] <= 25 THEN “Youth”
ELSIF [Age] <= 35 THEN “Adult”
ELSE “Mature”
ENDIF
4. SQL (CASE WHEN)
SELECT Age,
CASE
WHEN Age <= 17 THEN “Child”
WHEN Age <= 25 THEN “Youth”
WHEN Age <= 35 THEN “Adult”
ELSE “Mature”
END AS Age_Bucket
FROM TableName
Hope this helps!