topic Re: Linear Regression lines inside a scatter chart in QlikView

Linear Regression lines inside a scatter chart

Wed, 29 Jun 2011 17:03:26 GMT

I have a scatter chart as --

Dimension -- Client_Name (dots on the chart)

X-Axis -- Revenue

Y-Axis -- ACI

Now I need to add 2 linear regression lines in the chart. I tried adding a new expression using linest_m function and changing the display option as line but for some reason, the regression line is not being displayed..

Am I missign anything? If anyone has a sample prototype that shows scatter chart with regression lines and can share it, that would be very helpful!

Thanks in advance!

Linear Regression lines inside a scatter chart

johnw — Wed, 29 Jun 2011 18:10:34 GMT

For a linear trendline, checkmark "Advanced Mode" on the Expressions tab, and then checkmark the linear trendline. I don't know if that's what you mean, though.

I thought I had an example of calculating some boundary lines in a scatter chart, but I can't seem to find it. Maybe I'm misremembering.

Re: Linear Regression lines inside a scatter chart

Wed, 29 Jun 2011 18:54:39 GMT

John, thanks for quick reply!

Basically, I am trying build a chart as per attached imaeg... the image is a mockup designed in Excel.

The dots indicate - Client Names (thats the dimension for this scatter chart)

and the 2 expressions are - revenue and ACI

So far I am able to prepare the chart with client name dots and revenue and ACI being on X and Y axes.

Now as per QlikView documentation (section 21.4) about linear regression, I added 3rd expression as below --

=linest_m(total aggr(column(2),CMU2_NM),CMU2_NM) * CMU2_NM

+ linest_b(total aggr(column(2),CMU2_NM),CMU2_NM)

This obviouskly didnt work... my dimension represents a client name so not sure how I can apply the multiplication in above formula...

Now in terms of Trendlines, I wont be able to use that because I need to add 2 seperate linear regression lines... one for all the clients and another one for clients selected by filters applied..

Appreciate any help...

Re: Linear Regression lines inside a scatter chart

johnw — Wed, 29 Jun 2011 19:54:54 GMT

OK, I found the example I mentioned, and it's pretty similar to what you're doing. In the example, I calculated lines at 110% of the trend line and 90% of the trend line, but hopefully you can modify the line calculations to get your intended result. If you have trouble, I can take a closer look as what I did and how to adapt it.

Edit: Hmmm, I didn't even notice - I'm simulating a scatter chart with a line graph. Interesting.