Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone
I need to use the dimension displayed as a filter on the measure set analysis.
Material | level_0 | level_1 | level_2 | sales | discount |
Car | CAR | 15000 | 1000 | ||
Wheels | CAR | WHEELS | 2000 | 0 | |
Cabinet | CAR | CABINET | 5000 | 500 | |
Motor | CAR | MOTOR | 8000 | 500 | |
Valves | CAR | MOTOR | VALVES | 6000 | 0 |
Pipes | CAR | MOTOR | PIPES | 500 | 0 |
Paint | CAR | MOTOR | PAINT | 1500 |
500 |
I need to display a bar chart with a drill down of the levels as the dimension, and the (Sales - Discount) as the measure. The problem is that, when I have one level that repeat itself (motor in the example), the measure will sum all the rows in which motor appears in level_1, but I only need to sum where Material = Level_1 for example
Thanks in advance
Reading through this post may be helpful:
https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-where-Field-A-Field-B-query/td-p/382621
From above, if your fields are all in same table in your data model, you can use set analysis as referenced in that post. However, if your fields are in different tables, you may get weird results and may need to use an if statement inside of your sum instead.
Reading through this post may be helpful:
https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-where-Field-A-Field-B-query/td-p/382621
From above, if your fields are all in same table in your data model, you can use set analysis as referenced in that post. However, if your fields are in different tables, you may get weird results and may need to use an if statement inside of your sum instead.
Thank you Nicole,
Reading through this post gave me the insight I needed. I'll share my solution if anyone runs into a similar problem in the future.
sum({$<Material = {"=Material=$(vLastLevel)"}>}Sales-Discount)
where vLastLevel is a variable I created using
if(wildmatch('$(=GetCurrentSelections())', '*LEVEL_2*'),'LEVEL_2',
if(wildmatch('$(=GetCurrentSelections())', '*LEVEL_1*'),'LEVEL_2',
if(wildmatch('$(=GetCurrentSelections())', '*LEVEL_0*'),'LEVEL_1',
LEVEL_0))