0 Replies Latest reply: Jun 26, 2011 11:26 PM by AXON SG RSS

    0 values from calculations

      hi guys, i have attached a scrambled version of my table with regards to my question,

      http://i.imgur.com/m6aXy.jpg

       

      i'm trying to calculate the license usage rate by dividing the licenses used by the total licenses grouped by the license group, rank them and show their ranks

       

      however, as some of the rows have 0 for licenses used, it causes a hyphen or "-" to be displayed due to the 0 included in calculation. i know i can change or not show "-" but i wanna show it since 0 divide by whatever is still 0 meaning 0% used which is still meaningful to me.

       

      this is the dimension i've used for my pivot table,

      =(if(aggr(Rank(aggr(

      sum(LIC_USED)

      /sum(TOTAL_LIC)

      , LICENSE_GROUP)), LICENSE_GROUP)<= 10 or

      aggr(Rank(aggr(

      sum(LIC_USED)

      /sum(TOTAL_LIC)

      , LICENSE_GROUP)), LICENSE_GROUP) >=

      (Max(total aggr(Rank(aggr(

      sum(LIC_USED)

      /sum(TOTAL_LIC)

      , LICENSE_GROUP)), LICENSE_GROUP))- 10)

      , LICENSE_GROUP))

       

      the 2nd dimension of my pivot table is simply

      =Site

       

      while this is the expression for the ranks column,

      =aggr(Rank(aggr(

      sum(LIC_USED)

      /sum(TOTAL_LIC)

      , LICENSE_GROUP))

      , LICENSE_GROUP)

       

      PS: the picture i've uploaded is a reused one from another of my question, im unable to attach/upload a new one due to some restrictions in my current location