Hi Qlikers,
I have the following set of numbers AAA and BBB. BBB needs to be divided by AAA and produce a 'capped' result between 0-1, and then calculate an average of these capped values in a gauge chart.
| AAA | BBB | Uncapped | Capped |
| 53 160,00 | 3 074,00 | 5,78% | 5,78% |
| 76 788,00 | 3 996,00 | 5,20% | 5,20% |
| 106 600,00 | -13 536,00 | -12,70% | 0,00% |
| 76 908,00 | 22 180,00 | 28,84% | 28,84% |
| 97 440,00 | 25 256,00 | 25,92% | 25,92% |
| 103 440,00 | 3 956,00 | 3,82% | 3,82% |
| 62 556,00 | 23 952,00 | 38,29% | 38,29% |
| 71 404,00 | 30 873,00 | 43,24% | 43,24% |
| 74 688,00 | 2 346,00 | 3,14% | 3,14% |
| 103 440,00 | 3 502,00 | 3,39% | 3,39% |
| 102 644,00 | 3 593,00 | 3,50% | 3,50% |
| 71 404,00 | 9 031,00 | 12,65% | 12,65% |
| 106 600,00 | -13 060,00 | -12,25% | 0,00% |
| 83 044,00 | 38 523,00 | 46,39% | 46,39% |
| 83 044,00 | 49 012,00 | 59,02% | 59,02% |
| 55 660,00 | 60 585,00 | 108,85% | 100,00% |
| | | |
| | 22,69% | 23,70% |
In a straight table the capped average is trivial to accomplish, using the 'Total mode' functionality and selecting 'Average of Rows'. But how can I get the capped average in a gauge chart?
Any suggestions
Thanks
David
