8 Replies Latest reply: Feb 25, 2016 12:48 PM by Chris Luscombe RSS

    Variables, pivot chart heat maps, if statements,  and input boxes

    Chris Luscombe

      Hi Everyone,

      I am trying to make a pivot table with "heat map" of the values using the "background color expression".

       

      I was able to successfully does this using the following if statement in the "background color expression":

      If([Load Factor %] >= .85, Green(), If([Load Factor %] >= .70, LightBlue(), LightRed()))

       

      Resulting in a chart like this:

       

       

      However, you will note above that Green and Blue values are hard-coded at .85 and .70 respectively.  I want to make an input box where the user can decided was values will make some green or blue.  To attempt this I did the following:

      • downloaded an input box from Branch
      • Created two variables
        • vGreat, and set initial value to .85
        • vGood, and set initial value to .70
      • Changed if statement to: If([Load Factor %] >= $(vGreat), Green(), If([Load Factor %] >= $(vGood), LightBlue(), LightRed()))
      • Created two input box using branch extension
        • set first input box to use variable "vGreat"
        • set second input box to use variable "vGood"

       

      But my heat map pivot table just doesnt seem to react correctly.  It appears that the first input box works as expected, but then it appears whatever value I put in the second input box is just ignored.  Any ideas?

       

       

      I have attached the QVF I am working on, please refer to the "analysis tab".  There you will see the heat map with hardcoded values, heat map attempt with variables, and the two input boxes.

       

      Thanks!