topic Using Edit Module Functions in Pivot Table Expression in QlikView

Using Edit Module Functions in Pivot Table Expression

Thu, 30 Apr 2015 14:23:04 GMT

Hello

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,rgb(00,120,00),

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

else

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?

Re: Using Edit Module Functions in Pivot Table Expression

Gysbert_Wassenaar — Fri, 01 May 2015 06:11:21 GMT

No, you can't use macro's in chart expressions. Well, at least not without changing an easter egg setting called AllowMacroFunctionsInExpressions. But this is obviously not recommended or it would be allowed by default.

Why can't you use variables?

vShadeFactor: (255-128)/100

eCSH: count(StartHour)

ePercent: ($1/1999)*100

eRed: if( $(eCSH)< 1500, 0, if( $(eCSH)<2000, 128+$(vShadeFactor)*$(ePercent($(eCSH))) , 255))

eGreen: ....

eBlue: ....

cBackGround: RGB( $(eRed), $(eGreen), $(eBlue))

Re: Using Edit Module Functions in Pivot Table Expression

Fri, 01 May 2015 10:20:27 GMT

Thanks Gysbert.

Using Variables will make my initial attempt look a bit cleaner but won't provide the shading I'm after.

How do I change easter egg settings as I'm all for doing things that are not recommended?

Regards

Neil

Re: Using Edit Module Functions in Pivot Table Expression

Gysbert_Wassenaar — Fri, 01 May 2015 10:35:59 GMT

Menu --> Help --> About QlikView --> right click on qlik-symbol in bottom left corner

Re: Using Edit Module Functions in Pivot Table Expression

Fri, 01 May 2015 10:51:22 GMT

Result

Many Thanks

Re: Using Edit Module Functions in Pivot Table Expression

Gysbert_Wassenaar — Fri, 01 May 2015 11:03:37 GMT

Well, it's certainly colorful. I think I'd try more muted colors. It's a bit of an eyesore to me to be honest. And I'd probably give the ColorMix Wizard a go first.