1 Reply Latest reply: Apr 28, 2011 5:02 AM by Martina Brenner RSS

    Average calculation problem

    davidg

      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.

       

      AAABBBUncappedCapped
      53 160,003 074,005,78%5,78%
      76 788,003 996,005,20%5,20%
      106 600,00-13 536,00-12,70%0,00%
      76 908,0022 180,0028,84%28,84%
      97 440,0025 256,0025,92%25,92%
      103 440,003 956,003,82%3,82%
      62 556,0023 952,0038,29%38,29%
      71 404,0030 873,0043,24%43,24%
      74 688,002 346,003,14%3,14%
      103 440,003 502,003,39%3,39%
      102 644,003 593,003,50%3,50%
      71 404,009 031,0012,65%12,65%
      106 600,00-13 060,00-12,25%0,00%
      83 044,0038 523,0046,39%46,39%
      83 044,0049 012,0059,02%59,02%
      55 660,0060 585,00108,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