Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
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.
Maybe like
Avg({1<Category1 = {$(=Max({1}Category1))}, Category2 = {"*"} >} Measure)
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!
Avg({1<Category1 = {$(=Max({1}Category1))}, Category2 -= {"*"} >} Measure)
shouldn't it be like that? Or am I missing something?
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.
The second one worked but slightly modified:
Avg({<Category1 = {$(=Max({<Category2 = {"*"}>} Category1))}>} Measure)
Thanks!
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.