Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average of rows with condition

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:

CategoryMeasure
050
052
054
050
261
263
261
253


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

1 Solution

Accepted Solutions
sunny_talwar

May be like this:

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

View solution in original post

10 Replies
sunny_talwar

May be like this:

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

View solution in original post

manucamon
Specialist III
Specialist III

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

Not applicable

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

Not applicable

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)

Not applicable

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 1Category 2Measure
4Android50
0Android52
0Android54
4Android50
0Android61
0Android63
0IOS61
0IOS53
4IOS52
0IOS57
0IOS67
4IOS72
889
891
1294
893
892
1292

Thanks!

Osian

sunny_talwar

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

Not applicable

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