Solved

Case When in Custom Fields Dimension

  • 16 September 2021
  • 3 replies
  • 824 views

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
 

 

icon

Best answer by olga 16 September 2021, 15:52

View original

3 replies

Userlevel 1

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'))))))))

 

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. 

Userlevel 1

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#logical 

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

Reply