Case When in Custom Fields Dimension

[ramdani]

Hi,

How to create Custom Fields Dimension with Case when conditions
i have custom fields dimension
 

and the result is age list,
and i want to group the age list in custom fields dimension too
like 
 

case 
	when ${age}>0 and ${age} < 21 then '0-20'
        when ${age}>=21 and ${age} < 31 then '20-30'
        when ${age}>=31 and ${age} < 41 then '30-40'
        when ${age}>=41 and ${age} < 51 then '40-50'
        when ${age}>=51 and ${age} < 61 then '50-60'
        when ${age}>=61 and ${age} < 66 then '61-65'
        when ${age} >=66 and ${age} < 71 then '66-70'
        when  ${age} >70 then '>70'
        else '<60'
end

but error showen
 

[olga1 · Accepted solution]

Hiya!

You can use custom grouping for quick tiers creation: https://docs.looker.com/exploring-data/adding-fields/custom-measure#custom_grouping

Or you can use this syntax for CASE function: https://docs.looker.com/exploring-data/creating-looker-expressions/looker-functions-and-operators#lo... 

case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes), ..., else_value)

[Juanito_pregunt]

Thanks Olga. I just noticed that those features aren’t available in the release I work 21.6.53. Is there a way to achieve this in the release I have.

Thanks in advance. 

[olga1]

That’s right, both of them are pretty new.

As a workaround for earlier versions, I believe, we can use the nested IF function.

Something like 

if( (${age}>0 and ${age} < 21), '0-20', 
        if( (${age}>=21 and ${age} < 31),  '20-30', 
        if( (${age}>=31 and ${age} < 41) , '30-40',
        if(( ${age}>=41 and ${age} < 51 ), '40-50',
        if(( ${age}>=51 and ${age} < 61 ), '50-60',
        if(( ${age}>=61 and ${age} < 66 ), '61-65',
        if(( ${age} >=66 and ${age} < 71 ), '66-70',
        if(  ${age} >70 , '>70', '<60'))))))))