4 Replies Latest reply: Sep 16, 2012 11:54 AM by S H RSS

    How to calculate ratio of a value for a group compared to the group with the Max value

      In the attached data QVW, I have three groups, each of which has a cost. I'd like to evaluate the % of cost of each group relative to the group that has the max cost.

       

      For example, if the group with the largest cost is, say, $150, I want to compare other groups to this. If another group has a total cost of $75, I want an expression that will comapre these values and return 50%.

       

      I thought that using this expression would give me the desired results:

      SUM(Cost)

      /

      MAX( AGGR( SUM(Cost), Group))

       

      However, I see the denominator MAX( AGGR( SUM(Cost), Group)) is not giving me the Max Cost among all the groups.

       

      What expression would I use here? Is this a job for set analysis?

       

      Thanks for your help!