I am trying to save an average value of a measure into a variable. I want the value to be static and fixed.
Below is a simplified example of my case. I have limited the calculated index values for the each individual person with the max value of 200. Qlik calculates individual values for each person correctly, limiting the exceeding values individually. However if I try to put the average of all persons into a variable (using "=" with declaration of the variable, not using $ when calling it, to have a static value) this will cause it to provide a different result because of the maximum limit I have. Therefore the average is not correct. If I use to display the average on the table in Qlik, it will display the correct one. I am wondering how can I get this correct average value from the table into a fixed variable or are there any other tricks to getting this done?
As seen above, using the formula in a variable, where selection applies to both persons A and B -> returns the wrong result, because the Index never exceeds 200 when calculating for both persons together. Any ideas or workarounds?