The Function for Linest_M

[#1]

I am attempting to display multiple trend lines on a straight table and Chart through the use of the linest_M function.  Currently, the correct slope calculation appears only after I have selected Month

The below is the straight table:

Month	2015 Act	2015 Plan	Gap	Linest_M
Jan	50	100	-50
Feb	210	200	10
Mar	270	300	-30

My question is how do I display all linest_M values for each selected "Month" at the same time??

The trend lines is between Gap -- Month

My expression is

Gap = 2015 Act - 2015 Plan

=LINEST_M( RangeSum(Above(TOTAL Sum({$<PGroup={"Parts"}, PType={"Raw"} >} NettoVK1 ), 0, RowNo(TOTAL)))

-

Num(sum({$<PLGroup={"Parts"}, PLType={"Raw"} >} PAmount )*1000, '#.##'),Month)

[swuehl]

Please check the HELP for the linest_m function limitations when using nested aggregation.

I think you need to use advanced aggregation (the aggr() function ).

To better understand your setting, please post a small sample QVW and your expected outcome.

[#3]

Hi,

I checked the Help and updated the expression below:

LINEST_M(aggr(RangeSum(Above(TOTAL Sum({$<PGroup={"Parts"}, PType={"Raw"} >} NettoVK1 )-Num(sum({$<PLGroup={"Parts"}, PLType={"Raw"} >} PAmount )*1000, '#.##'), 0, RowNo(TOTAL))),Month),(aggr(rankMonth),Month)))