Master Dimension cannot be used in set analysis

[LK13]

Hello,

I have created a master dim. I want to use it in set analysis but since its not possible, is there any workaround?

MasterDim = If(len(trim(sale_amount_SUM_2)) = 0, 0, 'Exclude')

Set analysis: =Count ({< MasterDim = {0}, buy_service_nature -= {'PLANIFICATION'} >} Distinct sale_service_id)

This approach does not work and i do not want that.

Count({<sale_amount_SUM_2 = {"=if(sale_amount_SUM_2= 'null', 'null')"}>} Distinct sale_service_id)

Or I am supposed to use variable ? 

Thank you for your advice.

[sbaro_bd]

Hi @LK13 ,

The response is simple : you can use master item in a set analsyis. Set analysis only works with fields from your model, so you have to create this field in the script.

https://community.qlik.com/t5/New-to-Qlik-Analytics/Filtering-a-master-dimension-with-set-analysis/t....

Regards.

[anat]

create variable instead of masteritem ,then use variable in set analysis.

Var1=If(len(trim(sale_amount_SUM_2)) = 0, 0)

Count ({< sale_amount_SUM_2 = {"$(=Var1)"}, buy_service_nature -= {'PLANIFICATION'} >} Distinct sale_service_id)

[LK13]

HI @sbaro_bd , you are right, it would work if i was supposed to make brand new field into the script, but that is not what i wanted. Master item (master dim just works perfect) but i need it also in set analysis. This is why i was wandering if there is something i am not aware.

I also tried with a variable in similar way but does not work:

Count({<sale_amount_SUM_2 = {"$(vDimension1) = 'null'"}>} Distinct sale_service_id)

[diegozecchini]

Hi!
Since set analysis only works with fields, the most robust solution is to define your MasterDim logic in the load script as a calculated field. Example:

LOAD
*,
If(Len(Trim(sale_amount_SUM_2)) = 0, 0, 'Exclude') AS MasterDim
FROM [YourDataSource];

Then, you can reference MasterDim directly in your set analysis:

Count({<MasterDim = {0}, buy_service_nature -= {'PLANIFICATION'}>} Distinct sale_service_id)