0 Replies Latest reply: Jun 21, 2012 5:26 AM by elanir01 RSS

    need some help on pivot table based heat map

      hi,

       

      i have a pivot table with 3 dimensions:

      on the vertical i have project and vendor, while on the horizontal i have month.

       

      value displayed is a productivity calculation for each project,

      also there is a column that holds the project's productivity goal.

       

      now, im trying to do a heat map over this pivot that will get red background if under productivity goal and green if above.

      so far not so hard. i even found this discussion that helped me alot:

      http://community.qlik.com/thread/24347

       

      as suggested in the above link,

       

      i used the following expression:

       

      if(round(Sum (Delivery)/Sum (Time),.1)<pGoal,

                rgb(255,log(round(Sum (Delivery)/Sum (Time),.1))*75-255,log(round(Sum (Delivery)/Sum (Time),.1))*75-255),

                 if(round(Sum (Delivery)/Sum (Time),.1)>=pGoal,

                                rgb(255-log(round(Sum (Delivery)/Sum (Time),.1))*75,255,255-log(round(Sum (Delivery)/Sum (Time),.1))*75)))

       

      result is almost what im looking for.

      for values loower than the goal, the color range is going the wrong direction i.e darker colors for values close to goal and brighter colors for values far from goal.

      couldnt find how to reverse that expression.

       

      any suggestions?

       

      oh and there are no negative values at all....