Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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()))