Migrate expression to Macro

Mon, 07 Jan 2013 23:05:45 GMT

I'm currently setting the background color of a chart through an expression (on the 'expressions' tab) like this, and it works:

=if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) >=.95, rgb(0,255,0),

if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag) < .95 AND

Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) >=.85), Yellow(),

if(Num(Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag)) < 85, rgb(252,0,0),

White())))

My problem:

I have a *bunch* of charts where I'm repeatedly using this same expression, or some close variation.

What I'd like to accomplish:

I'd like to encapsulate this code in a function/macro for reusability and maintainability. I'd like to create a function that accepts the chart (or object id, whatever) as a parameter, and then does something like this (my best attempt):

function ZSetBackgroundColor(parmObjectName)

{

try {

// How to do this? I want this to be parmObjectName instead, but

// was trying it out hard-coded for proof-of-concept purposes here:

var sumOfField = ActiveDocument.GetField('M6').getSelectedValues(); //This doesn't work

var x = sum(sumOfField); //?

var countOfField = Count(ActiveDocument.GetField('M6').MyFieldToCount); // How to do this?

var valueToCalc = sumOfField / countOfField;

var retColor = White();

switch (valueToCalc) {

case valueToCalc >= 95:

retColor = Green();

case (valueToCalc >= 85) && (valueToCalc <=94):

retColor = Red();

case valueToCalc <= 84:

retColor = Yellow();

default:

retColor = White();

}

return retColor;

} catch (err) {

alert(err);

}

And from the chart's expression, I want to do this:

=ZSetBackgroundColor(theChartObjectNameOrId_or_Whatever)

Other Notes:

Trying to do something like...

var x = Sum({$<Measure={'M6'}>}Flag)/Count({$<Measure={'M6'}>}Flag);

...within the macro doesn't work. It always gives me a syntax error that ":" is missing. But I'm thinking this isn't the right way to do it, anyway.

Any info would be greatly appreciated.

Thanks!

Re: Migrate expression to Macro

michael_anthony — Tue, 08 Jan 2013 06:00:26 GMT

I assume your trying to code your macro in JScript. May want to try doing it in VBScript instead as that seems to be more popular and easier for people to help with.

I can see a problem with why your var x = Sum(....) doesn't work. In the script you don't have access to qlikview functions, only those in the scripting language itself.

However the document object does have an Evaluate (or evaluateEx) method which allows you to put in a qlikview expression and have it return the result.

If you use that method should help with what your trying to do. Check out the API Guide that comes with Qlikview installation doco for more info.

Re: Migrate expression to Macro

Anonymous — Tue, 08 Jan 2013 08:44:20 GMT

Variables can also be used for this as they accept paramaters through the syntax $<paramater number>.

$(=Sum({$<Measure={$1}>}Flag)/Count({$<Measure={$1}>}Flag))

You can then call this variable, passing in your one measure.

=$(TheVariableName('M6'))

See attached example.

Jonathan

topic Re: Migrate expression to Macro in QlikView

Migrate expression to Macro

Re: Migrate expression to Macro

Re: Migrate expression to Macro