topic Re: Adapt my expression to work on a chart in QlikView

Adapt my expression to work on a chart

brunolelli87 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Adapt my expression to work on a chart

brunolelli87 — Mon, 01 Jun 2020 00:33:24 GMT

Any idea guys?

Re: Adapt my expression to work on a chart

brunolelli87 — Mon, 01 Jun 2020 07:07:59 GMT

How can I do that?

Re: Adapt my expression to work on a chart

agigliotti — Mon, 01 Jun 2020 07:42:36 GMT

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.

Re: Adapt my expression to work on a chart

brunolelli87 — Mon, 01 Jun 2020 14:06:49 GMT

Hello,

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

Any other idea?

Re: Adapt my expression to work on a chart

agigliotti — Mon, 01 Jun 2020 14:31:46 GMT

could you share a sample app with mock data?

Re: Adapt my expression to work on a chart

brunolelli87 — Mon, 01 Jun 2020 14:50:36 GMT

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

Re: Adapt my expression to work on a chart

agigliotti — Mon, 01 Jun 2020 15:26:29 GMT

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

Re: Adapt my expression to work on a chart

brunolelli87 — Mon, 01 Jun 2020 15:55:01 GMT

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