topic Re: Average with Multiple Conditions in QlikView

Average with Multiple Conditions

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Average with Multiple Conditions

swuehl — Mon, 05 Sep 2016 18:47:31 GMT

Maybe like

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

Re: Average with Multiple Conditions

Tue, 06 Sep 2016 10:30:11 GMT

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!

Re: Average with Multiple Conditions

jjbom1990 — Tue, 06 Sep 2016 14:27:17 GMT

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

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

Re: Average with Multiple Conditions

swuehl — Tue, 06 Sep 2016 16:14:11 GMT

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.

Re: Average with Multiple Conditions

Tue, 06 Sep 2016 17:03:26 GMT

The second one worked but slightly modified:

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

Thanks!

Re: Average with Multiple Conditions

swuehl — Tue, 06 Sep 2016 19:09:36 GMT

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.