Get expression value using Macro.

[#1]

I have an expression that I need to reference in my Macro.

Basically, I had about 120 nested Ifs in the background color of one of my expressions, and Qlikview doesn't allow that many. So, I decided to write a macro instead. I need to reference the value of the expression in my macro, but I can only find ways to set expression properties, not reference the values themselves.

My If statements looked like:

If(Customer = 'A'

and((Label = 'Metric A' and Expression < .95)

or(Label = 'Metric B' and Expression < .96)),red(),

If(Customer = 'B'

and((Label = 'Metric B' and Expression < .99)

or(Label = 'Metric C' and Expression < .96)

or(Label = 'Metric D' and Expression < .90)),red(), .........

And I'm attempting to write my macro as:

sub MakeRed

set Customer = ActiveDocument.Fields("Customer").GetSelectedValues

If Customer.Item(i).Text = "A" Then

  Set Labels = ActiveDocument.Fields("Label").GetPossibleValues

  For i = 0 to Labels.Count

  If Labels.Item(i).Text = 'Metric A' and  //here's where I need to reference the expression// ExpressionValue < .95)......

  Next

End If

End sub

Any help with this problem or a better suggestion would be much appreciated.

[prieper]

Macros are usually not that welcomed ...

Isn't there a better solution in order to replace these nested IF's? Can you do something with flagging and/or mapping?

Can you post an example?

Peter

[#3]

I posted an example of the nested IFs in my original question:

If(Customer = 'A'

and((Label = 'Metric A' and Expression < .95)

or(Label = 'Metric B' and Expression < .96)),red(),

If(Customer = 'B'

and((Label = 'Metric B' and Expression < .99)

or(Label = 'Metric C' and Expression < .96)

or(Label = 'Metric D' and Expression < .90)),red(), .........

If there is a better solution could you clue me in?

[prieper]

A bit more info might be helpful.

In a first approach:

You may put this into a separate table, like

LOAD * INLINE [Customer, Label, Expression

                         A, MetricA, .95

                         A, MetricB, .96

                         B, MetricB, .99

                         B, MetricC, .96

                         etc];

This table to be linked to the facttable (Customer, Label).

Instead of "synthetic keys", you may also create a concatenated key (AUTONUMBER(Customer&Label) AS KEY).

Backgroundcolor then might be: IF(SUM(Sales) > Expression, RED()))