Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
the expression is the same, the only thing you have to do is to enter a dimension for the Total like:
Sum(AAA)/SUM(Total <Dimension1,Dimension2,...> BBB)