need some help on pivot table based heat map

Thu, 21 Jun 2012 09:26:03 GMT

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....

topic need some help on pivot table based heat map in QlikView

need some help on pivot table based heat map