topic Re: What use into Linest_m function? in QlikView

What use into Linest_m function?

Wed, 01 Apr 2015 07:41:19 GMT

I am working on a chart to have the price behaviour over 5 years. What I need is to have always shown all the columns for different years and months so I used set analysis like below:

=SUM({<yearmonth=,Anno=,Mese=>} vSold)/SUM({<yearmonth=,Anno=,Mese=>} Quantità)

and it works as I need.

When I select the trendline on chart it gives me "y=6.6319x + 4446.9"

since I have to use the "m" into y=xm+q to understand if trend is increasing or flat then I used the lines_m function but it does not work.

Any idea?

I have already tried to check into the forum but I am not able to find a solution.

Thanks

Massiimo

Re: What use into Linest_m function?

Gysbert_Wassenaar — Wed, 01 Apr 2015 09:47:55 GMT

The trendline formula uses the index number of the x-axis dimension values, i.e the left most value is 1, the next 2 etc. The linest_m function uses the actual x-axis dimension values. You could try:

linest_m(aggr( sum(....vSold...)/sum(....Quantita...) , yearmonth), aggr( rowno(), yearmonth))

See attached example.

Re: What use into Linest_m function?

Wed, 01 Apr 2015 12:12:50 GMT

Thank you Gysbert.

It works perfectly!

The step "aggr( rowno(), yearmonth))"

was what I missed. Your explaination has been very clear.

Thank you

Massimo

Re: What use into Linest_m function?

Wed, 01 Apr 2015 12:32:51 GMT

Gysbert,

one more thing:

yearmonth is used as label for the x-axis so the above formula works properly. However even Anno and Mese are two fields which affect the selection of yearmonth (even if they do not change the chart).

Is there any way to use a multiple fields into aggr function so I can make the Linest_m formula insensitive to Anno nor Mese?

Thankx

Re: What use into Linest_m function?

Gysbert_Wassenaar — Wed, 01 Apr 2015 13:37:08 GMT

You can make the linest_m value insensitive to selections in other fields. But I don't think that's going to help you. If you don't want the chart to change then create an alternate state on the General tab of the Document Properties window and then assign that alternate state to the chart on the General tab of the charts properties window.

Re: What use into Linest_m function?

Wed, 01 Apr 2015 15:05:06 GMT

The chart is not affected by my selection of Anno nor Mese since I used the yearmonth field as x-axis but through the set analysis I made the chart insensitive also to Anno and Mese:

=SUM({<yearmonth=,Anno=,Mese=>} vSold)/SUM({<yearmonth=,Anno=,Mese=>} Quantità)

the problem is that Linest _m formula works by entering

=linest_m(aggr( sum({<yearmonth=,Anno=,Mese=>} vSold)/SUM({<yearmonth,Anno=,Mese=>} Quantità) , yearmonth), aggr( rowno(), yearmonth))

but Anno and Mese affect the value of "m". I would like to make it insensitive.

Maybe I did not understand your indication (I am not an expert) but do you have any solution for my issue?