topic Re: Using macro to create and change labels on a graph. in QlikView

Using macro to create and change labels on a graph.

Thu, 02 Apr 2015 22:02:10 GMT

Hey All,

I have already created a macro that will dynamically create expressions based on user's selection (only 3 list boxes have this capability). I have been having a huge issue trying to get it to also change the label without the graph freaking out. Below you'll find the macro code. I have tried stuff like:

e = Graph.GetProperties.Item(0).Item(0).Data.ExpressionVisual

e1 = e.label.v

e1 = "LABEL"

but it just refuses to work. I would like to do it right after I make the expression. Below is the code for the macro.

sub redraw()

ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report

'EXPRESSIONS

set graph = ActiveDocument.getSheetObject("CH54")

count = 1

set gp = graph.getProperties

set prodValues = ActiveDocument.getField("Product Family").GetSelectedValues

set buValues = ActiveDocument.getField("BU").GetSelectedValues

set blValues = ActiveDocument.getField("Build Location").GetSelectedValues

while count > 0

count = graph.RemoveExpression(0)

Wend

If prodValues.count = 0 and buValues.count = 0 and blValues.count = 0 then

graph.addExpression "Count(distinct [Sales Order])"

Else

for i = 0 to blValues.count-1

graph.addExpression "Count(distinct {1<[Build Location] = {" & blValues.Item(i).Text & "}>} [Sales Order]"

'ADD LABEL

for j = 0 to buValues.count-1

graph.addExpression "Count(distinct {1<[BU] = {" & buValues.Item(j).Text & "}>} [Sales Order])"

'ADD LABEL

for k = 0 to prodValues.count-1

graph.addExpression "Count(distinct {1<[Product Family] = {" & prodValues.Item(k).Text & "}>} [Sales Order])"

'ADD LABEL

end if

end sub

Re: Using macro to create and change labels on a graph.

petter — Thu, 02 Apr 2015 23:08:37 GMT

GetProperties method must be follow by a SetProperties to store the properties that has been assigned to the object that you did the GetProperties from...

Assignment of individual properties does not take effect until you do a final SetProperties.

Search for SetProperties in the API Guide and look at the example code you find there.

Re: Using macro to create and change labels on a graph.

Fri, 03 Apr 2015 03:29:54 GMT

I was doing that before. Every time I add a label I set the properties and then get them before hand. I'll take a look tomorrow and update with any problems.

Re: Using macro to create and change labels on a graph.

rwunderlich — Fri, 03 Apr 2015 03:48:56 GMT

Be forewarned that SetProperties is not supported in the Server environment and will sometimes give unpredictable results. (Section 81.2 of the Server Ref Guide). It's a bummer when you get it polished on the desktop and then it flakes out in the Server.

You can generally achieve the same user report effect with conditional expressions and dollar sign expansions.

--Rob

Re: Using macro to create and change labels on a graph.

Fri, 03 Apr 2015 13:22:39 GMT

Rob,

I can't use conditional expressions in this case because I would have to create an extremely large number upfront. Also, I'm not sure the dollar sign expansions would work because I don't want to sum two values of a dimension if both are selected. Instead, I want to graph them as separate lines. Do you have any thoughts on how to approach this, in order for it to work from the server?

Thank You

Re: Using macro to create and change labels on a graph.

rwunderlich — Fri, 03 Apr 2015 15:51:17 GMT

Can you post a small sample qvw? Doesn't have to be real data.

Re: Using macro to create and change labels on a graph.

Fri, 03 Apr 2015 16:09:25 GMT

I have attached to the original post. The graph I am trying to do is on there along with the three list boxes that trigger an action. The macro is also in the qvw.