Case Statement in Dimension Qlik Sense

[wdchristensen]

Hi Qlik Community, I am trying to create age groupings for a dimension. In this example, I want to count customers in each age category. Currently I am making these derived category types in the load script but occasionally the business wants to test how things would look, given different ranges. This is a bit of a hassle to modify the script just to preview the data. How can the case statement below be rewritten to work in the formula for the dimension for a given visualization? 

CASE
   when age <18 then 'Under 18'
   when age between 18 and 24 then '18-24'
   when age between 25 and 34 then '25-34'

   when age > 34 and 34 then '34 Plus'
END

[#2]

Hi William,

Try the expression below:

if(age < 18, 'Under 18',

if(age >=18 and age<=24, '18-24'),

if(age >=25 and age<=34, '25-34'),

if(age >34, '34 Plus))

[rwunderlich]

Since only 1 if() will be true, you can skip the "and" by writing it in this order:

if(age > 34, '34 Plus'

,if(age >= 25, '25-34'

,if(age >= 18, '18-24'

,'Under 18'

)))

-Rob

[RSvebeck]

If you can accept a stable interwall width over the complete range, you could use the class function with a variable for its second parameter, allowing users to adjust the intervall width themselves as they are doing their analysis.

Robert

[wdchristensen]

Hi Robert,

I am very familiar with the nested “if” solution and I was look for something cleaner. My initial question provided a poor example. A better example would be distance between the home office and the customer home office in 25 mile increments. This will potentially have hundreds of categories and would require a lot of changes if the user wanted to see the categories by 50 mile increments. Can you provide an example of the class solution? I am looking more for a method that maximizes scalability. Thanks for your help!

[RSvebeck · Accepted solution]

To get familiar with the class() function, Look at the video of  Michael Tarallo https://community.qlik.com/videos/4326 

The second parameter in the class function could be a user related value, so for one user it can be 50, and another it can be 25. If you need more help I could make a small example.

//Robert

[RSvebeck]

Here is an example. 🙂

Svebeck Consulting - Flexible Class Function in Qlik Sense