Adapt my expression to work on a chart

[brunolelli87]

Hello guys,

I have the following expression working perfectly to identify the slope of my linear regression.
So, when the user selects a company, the slope is shown on a Label Box.

My intention with the following expression is to show the slope of average points of VDAF per date (DVPG) starting from 18/03/2020.

=Num(LINEST_M(Aggr(avg(If(DVPG > '18/03/2020', VDAF)), DVPG),DVPG), '#.##0,0000;-#.##0,0000')

Now I would like to have a chart with Companies (STCK) as a Dimension and the expression mentioned above as Expression. 

How could I adapt the above expression to work with STCKs as dimension?

Thanks

[brunolelli87]

Any idea guys?

[brunolelli87]

How can I do that?

[agigliotti]

maybe this:
=Num( LINEST_M(Aggr(avg( If(DVPG > '18/03/2020', VDAF) ), STCK, DVPG ), STCK ), '#.##0,0000;-#.##0,0000' )

I hope it can helps.

[brunolelli87]

Hello,

Thanks for your help but it did not work.
"No data to display" was the output

Any other idea?

[agigliotti]

could you share a sample app with mock data?

[brunolelli87]

Yes, sure!

Please, find attached my project!

I'm using a Personal Edition of QlikView so, I'm not able to open any file you may send me back!
I'm sorry for that...

Thanks for your help

[agigliotti · Accepted solution]

try this:
= LINEST_M(Aggr(avg( If(DVPG > '18/03/2020', VDAF) ), STCK, DVPG ), DVPG )

[brunolelli87]

Wow!!!

It's working now! Thank you so much.

I don't want to upset you with lots of questions, but how could I convert your expression to show the average of it's group?

= LINEST_M(Aggr(avg( If(DVPG > '18/03/2020', VDAF) ), STCK, DVPG ), DVPG )

Check the following Example: For all the other charts, I'm doing this:

For each STCK:

FirstSortedValue(VDAF, -DVPG)*FirstSortedValue(NDAN, -PERI)

For STCKs group:

AVG( TOTAL {<STCK,SETO=p(SETO)>}
	(
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}VDAF, -DVPG), STCK, SETO) *
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}NDAN, -PERI), STCK, SETO)
	)
)

Thanks