Reference another field as dimension in set analysis

[gitguto]

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

[Nicole-Smith · Accepted solution]

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.

[gitguto]

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