Average with Multiple Conditions

[#1]

I have data in the below format (this has been hugely simplified)

I want a Qlik expression to get the Average of Measure with the following conditions:

• Only calculate across the maximum values of Category1
• Only calculate where Category2 is not null

I also want it to to look at all the data and not just the current selection.

So it should be the average of the highlighted rows below, so AVG(50,50,52,72) = 56

Category 1	Category 2	Measure
4	Android	50
0	Android	52
0	Android	54
4	Android	50
0	Android	61
0	Android	63
0	IOS	61
0	IOS	53
4	IOS	52
0	IOS	57
0	IOS	67
4	IOS	72
8		89
8		91
12		94
8		93
8		92
12		92

Thanks!

[swuehl]

Maybe like

Avg({1<Category1 = {$(=Max({1}Category1))}, Category2 = {"*"} >} Measure)

[#3]

Thanks for your response, I get a null answer for this. I think it's because it's trying to find the maximum of Category1 across all rows (which is Category1 = 12) and then not null for Category 2 (for which there are no records).

How can I modify it so that it performs Condition2 first (non null for Category 2) and then of these perform Condition 1 (maximum)?

Thanks!

[jjbom1990]

Avg({1<Category1 = {$(=Max({1}Category1))}, Category2 -= {"*"} >} Measure)

shouldn't it be like that? Or am I missing something?

[swuehl · Accepted solution]

Maybe

Avg({1<Category1 = {$(=Max({1<Category2 = {"*"}>} Category1))}, Category2 = {"*"} >} Measure)

It could also be enough to just use

Avg({1<Category1 = {$(=Max({1<Category2 = {"*"}>} Category1))} >} Measure)

depending on your data.

[#6]

The second one worked but slightly modified:

Avg({<Category1 = {$(=Max({<Category2 = {"*"}>} Category1))}>} Measure)

Thanks!

[swuehl]

You're welcome.

I've used the set identifier 1 in the set expression because you explicitely stated:

I also want it to to look at all the data and not just the current selection.