Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Get expression value using Macro.

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.

Tags (1)
3 Replies
Highlighted
Master II
Master II

Re: Get expression value using Macro.

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

Highlighted
Not applicable

Re: Get expression value using Macro.

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?

Highlighted
Master II
Master II

Re: Get expression value using Macro.

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()))