topic Re: The Function for Linest_M in QlikView

The Function for Linest_M

Thu, 08 Oct 2015 07:31:32 GMT

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
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)

Re: The Function for Linest_M

swuehl — Thu, 08 Oct 2015 08:05:27 GMT

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.

Re: The Function for Linest_M

Thu, 08 Oct 2015 08:21:11 GMT

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)))