Average of rows with condition

[#1]

Hi,

I'm hoping this is a simple one as I'm new to Qlik expressions.

I have a table which (in a very simplified form) has the following data:

Category	Measure
0	50
0	52
0	54
0	50
2	61
2	63
2	61
2	53

I want the calculation to calculate the average of the Measure only for the Max category. So here it would be the average of 61,63,61,53 = 59.5.

Note: This doesn't need to be independent of the selection of the visual or anything, so if the current selection only returns the above rows it shouldn't look at rows outside this selection.

Thanks

[sunny_talwar · Accepted solution]

May be like this:

Avg({<Category = {$(=Max(Category))}>}Measure)

[Anonymous]

Avg({<Category = {$(=Max({1}Category))}>}Measure)

[#4]

Thank you! That worked

I'm not getting the $,<>,{ parts of the syntax - if you can point me at a good intro to set analysis that would also be great

[#5]

What if I wanted to include:
(1) All data

(2) All but current selection?

[sunny_talwar]

You can read about set analysis in the attached document

[sunny_talwar]

May be these:

1) Avg({1<Category = {$(=Max({1}Category))}>}Measure)

2) Avg({1-$<Category = {$(=Max({1-$}Category))}>}Measure)

[#8]

Thanks Sunny ,

Final question, I have another Category variable, Category2 as below.

I now want it to get the Average of Measure where the values for the max values of Category1 (as before) but also where Category2 is not null - i.e. there are two conditions.

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

So it should be the average of the highlighted rows, 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!

Osian

[sunny_talwar]

It seems that you need the average of rows where Category 1 = 4? Is this true?

[#10]

Not necessarily, this is a much simplified version of the table. So it needs to be the max values of Category1 where Category2 is not null.

Thanks again, appreciate your help on this