5 Replies Latest reply: May 1, 2015 7:12 AM by Gysbert Wassenaar RSS

    Using Edit Module Functions in Pivot Table Expression


      I am a newcomer to QlikView and I've ran into a slight problem.


      As part of getting to know the product I have been trying to produce a heat map using a Pivot table and I have been experimenting with ways in which to change the background colour of each cell depending on the value within the cell.


      Initially I used the visual queues tab and set the upper an lower limit.  This gave me the Green/Yellow/Red but it was not exactly what I was after as I want a gradual shading from Green to yellow, then yellow to red.


      So within the Background Color Definition in the Expressions tab of the Pivot Table properties I added the following code to introduce a few more shades. 


      if(count(StartHour)>=500 and Count(StartHour)<1000, rgb(00,150,00),

      if(count(StartHour)>=1000 and Count(StartHour)<1500, rgb(00,255,00),

      if(count(StartHour)>=1500 and Count(StartHour)<2000, rgb(128,255,00),

      if(count(StartHour)>=2000 and Count(StartHour)<3000, rgb(255,255,00),

      if(count(StartHour)>=3000 and Count(StartHour)<4000, rgb(255,244,0),

      if(Count(StartHour)>=4000 and Count(StartHour)<4500, rgb(255,213,0),

      if(Count(StartHour)>=4500 and Count(StartHour)<5500, rgb(255,128,0),

      if(Count(StartHour)>=5500 and Count(StartHour)<6500, rgb(255,103,0), rgb(255,0,0)  )))))))))


      This now looks a bit better but I thought it might be a bit more interesting to create a custom function within the edit module.  And here is where my issue lies.  I can use these functions from within the script editor when I'm loading data, but I can't use these functions from with any of the expression fields in the pivot table properties.  Having tried a few other things like text boxes, I can't get the functions in the edit module to work from any expression field.


      This is my Function (VBScript):

      Function GetRedShade (processValue)

      REM Return a red shade for the heat map pivot table

      DIM redValue

      DIM percent, shadeFactor

      if processValue < 1500 then

        redvalue = "00"

      elseif processValue >=1500 and processValue < 2000 then

        percent = (processValue/1999)*100

        shadeFactor = (255-128)/100

        redValue = (percent*shadeFactor)+128


        redValue = 255

      end if

      GetRedShade = redValue

      end function


      So when I call this from the background color definition field like this

      rgb(GetRedShade(Count(StartHour)), GetGreenShade(Count(StartHour)), 0)


      I get Error in expression.  So can anybody help?