Defining Non Mutually Exclusive Columns Using Set Expressions

[bruchar]

Hi Qlik Community, 

I am creating some metrics using the new Pivot Table (from custom objects) and need to create three dimension (columns) which are not mutually exclusive. My rows are several KPI measure calculated in Qlik set expressions which should calculate for each column. 

I tried using the below nest IF implementation to create my three columns, but due to the nature of IF statements only the first column ever gets created. Any thoughts on how else I might approach this?  

IF(Country = 'US', 'US Overall', /*Column 1*/

IF(Country= 'US' AND home_type= 'House', 'US - House', /*Column 2*/

IF(Country= 'US' AND home_type, 'Apartment', 'US - Apartment' /*Column 3*/

)))

Thank you!

[Lisa_P]

Create them the other way around:

IF(Country= 'US' AND home_type= 'Apartment', 'US - Apartment' /*Column 3*/

IF(Country= 'US' AND home_type= 'House', 'US - House', /*Column 2*/

IF(Country = 'US', 'US Overall', /*Column 1*/

)))

[bruchar]

Thank you for the suggestion Lisa. Unfortunately, If I create them the other way around then only columns 1 and 2 are created. Column 3 (US - Overall) is omitted. 

[Vegar]

@bruchar 

An if statement always ends when the set condition is true. In your case all evaluations where Country = 'US' will end at the first IF condition no matter which home_type .

Try @Lisa_P 's suggestion to se I that is what you are looking for.

If not then consider creating real dimensions in the script to fit you needs or moving the logic away from dimensions and into the measure using set analysis. The drawback is that you will need three measures for every current measure to achieve this.

[Lisa_P]

Sorry, I just realized that the first 2 combined to the total of the third. 

Why don't you use the dimension of home_type and add Country=US into your set analysis measure expressions. You could then use totals for home_type.