topic Show Equation via macro in QlikView

Show Equation via macro

Fri, 07 May 2010 16:16:19 GMT

Hi All,

I create a macro that add a trend line to a chart. How can I add the trend line equation via macro?

This is the macro:

sub add_lintrendline
set chart = ActiveDocument.GetSheetObject( "CH50" )
chart.AddTrend 1,1,1
set p = chart.GetProperties
p.ChartProperties.TrendLineWidth = 5
chart.SetProperties p
end sub

Thanks

Show Equation via macro

Fri, 07 May 2010 18:29:57 GMT

The trendline equation is defined by the parameters passed to AddTrend. The first parameter tells you which expression to trend. The second is the type of trend (1 for polynomial, 2 for exponential). Then if you choose polynomial, the third parameter tells you which degree.

So if you want to set a Polynomial of the 2nd Degree on the first expressions, you would use:

AddTrend(0,1,2)

The third parameter is (only when using 1 as the second parameter):
0 Mean
1 Linear
2 Polynomial 2nd Degree
3 Polynomial 3nd Degree
4 Polynomial 4nd Degree

Show Equation via macro

Mon, 10 May 2010 08:22:21 GMT

Thank you for the explanation.

But I want to show (via macro) the equation on the chart, like this:

Show Equation via macro

Mon, 10 May 2010 22:05:22 GMT

I don't know that it is possible to get that equation into a macro. I did not see anything in the API that would allow that. It could be available but undocumented.

If you really wanted to make your life difficult, you could probably determine the equation of the trend line yourself. There are probably equations out there to determine a trend line. I would probably tell the users it was impossible before I tried that though.

Show Equation via macro

Tue, 11 May 2010 08:53:55 GMT

I found this example in API guide, but I think that it is not useful for me:

set chart = ActiveDocument.ActiveSheet.CreateBarChart
chart.AddDimension "ProductType"
chart.AddExpression "count(Customer)"
set p = chart.GetProperties
set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
expr.ShowAsBar = true
rem add linear regression trend line
expr.ExpressionTypes.Add
expr.ExpressionTypes(0).Type = 1
expr.ExpressionTypes(0).Order = 1
expr.ExpressionTypes(0).DisplayEquation = true
chart.SetProperties p

Any ideas????

Show Equation via macro

Anonymous — Tue, 11 May 2010 10:19:12 GMT

That should be the one. The DisplayEquation member should allow you to set the trendline to be shown or hidden as it's read-write.

expr.ExpressionTypes(0).DisplayEquation = true

Just make sure that you assign the correct expression to expr.

In this example it's set to the first expression (number 0):

set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual

Show Equation via macro

Tue, 11 May 2010 11:19:47 GMT

Thanks Johannes.

It's works for average, lineare and polynomial (2nd, 3th and 4th degree) but not for exponential, to do this I use this:

expr.ExpressionTypes.Add
expr.ExpressionTypes(0).Type = 2
expr.ExpressionTypes(0).Order = 1
expr.ExpressionTypes(0).DisplayEquation = true

but the exponential trend doesn't appear. Why?

Show Equation via macro

mantaq10 — Tue, 10 Aug 2010 17:40:53 GMT

Hi all,

Interesting thread..

i was wondering if i can reuse the linear regression equation elsewhere. That is, i am looking for a function which extracts the equation as a string for me to treat it as an expression? something like the linest_* family of functions?

Actually, i dont get the same regression equation when i generate it as a trendline from the chart properties and when i run the functions on the same two fields.