How to find slopes of trendlines for multiple series in a straight table? Linest_m?

[#1]

Hi,

I would like to compute the slope of linear trendlines of many series and keep in a straight table. 

For example, if I have 10 months of data for 3 cities, can I create a table that consists of 3 rows for the cities and a column that shows the different slopes for each of the linear trendlines.  I think I should use Linest_m but I get confused by the TOTAL and the AGGR function embedded in it. 

Can someone please help?  (See tables below)

Thanking in advance,

Anthony

Desired Results Table:

City	Slope
Albany	31.29
Cleveland	18.31
Jacksonville	46.02

Raw Data Table:

MonthNo	City	Sales	Slope
1	Albany	2,317	31.29
2	Albany	2,751	31.29
3	Albany	2,347	31.29
4	Albany	2,405	31.29
5	Albany	2,586	31.29
6	Albany	2,407	31.29
7	Albany	2,561	31.29
8	Albany	2,526	31.29
9	Albany	2,681	31.29
10	Albany	2,814	31.29
1	Cleveland	1,848	18.31
2	Cleveland	2,108	18.31
3	Cleveland	1,808	18.31
4	Cleveland	1,767	18.31
5	Cleveland	1,889	18.31
6	Cleveland	1,841	18.31
7	Cleveland	2,031	18.31
8	Cleveland	1,933	18.31
9	Cleveland	2,001	18.31
10	Cleveland	2,115	18.31
1	Jacksonville	2,362	46.02
2	Jacksonville	2,734	46.02
3	Jacksonville	2,239	46.02
4	Jacksonville	2,387	46.02
5	Jacksonville	2,594	46.02
6	Jacksonville	2,529	46.02
7	Jacksonville	2,865	46.02
8	Jacksonville	2,646	46.02
9	Jacksonville	2,674	46.02
10	Jacksonville	2,874	46.02

[jonathandienst]

Hi

Something like

=Linest_m(Aggr(Sum(Sales), City, MonthNo), MonthNo)

Hope that helps

Jonathan

[#3 · Accepted solution]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony

[#4]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony

[#5]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony

[#6]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

         http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony

[#7]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

         http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony

[#8]

Dear All,

I found the answer to my question when I discovered the following thread after I had posted:

         http://community.qlik.com/message/72194#72194

John WItherspoon did an excellent job explaining the Linest_M and Aggr functions.

Thanks!

Anthony