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 with Multiple Conditions

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

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

6 Replies
swuehl
MVP
MVP

Maybe like

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

Not applicable

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

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

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

swuehl
MVP
MVP

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.

View solution in original post

Not applicable

The second one worked but slightly modified:

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

Thanks!

swuehl
MVP
MVP

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.