Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this:
Avg({<Category = {$(=Max(Category))}>}Measure)
Avg({<Category = {$(=Max({1}Category))}>}Measure)
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
What if I wanted to include:
(1) All data
(2) All but current selection?
You can read about set analysis in the attached document
May be these:
1) Avg({1<Category = {$(=Max({1}Category))}>}Measure)
2) Avg({1-$<Category = {$(=Max({1-$}Category))}>}Measure)
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
It seems that you need the average of rows where Category 1 = 4? Is this true?
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