Using average with FirstSortedValue

[brunolelli87]

Hello

I would like to create a chart to compare the company's result with its segment's result.

So, the Dimension is STCK, which represents the Companys.

And the expression for the Company's value is:

=FirstSortedValue(VDAF, -DVPG)/(FirstSortedValue(LOPP, -PERI)/FirstSortedValue(NDAN, -PERI))

I'm trying to create the other bar for the average of its segment. The segment field is called SETO, and I'm trying to use the following expression:

=avg({<STCK,SETO=p(SETO)>}FirstSortedValue(VDAF, -DVPG)/(FirstSortedValue(LOPP, -PERI)/FirstSortedValue(NDAN, -PERI)))

But it's not working, and I think the problem is that avg and FirstSortedValue are aggregation functions...

So, how can I fix it?
Thanks

[sunny_talwar]

Would you be able to explain this by some sample data and the output you expect to see from it?

[brunolelli87]

Yes, 

Of course, I can...

So, this is how my QlikView file is structured:

I have one table called Stocks:

Table Stocks:
DVGP	STCK	VDAF	KKEY
22/05/2020	AZUL4	10,65	22/05/2020AZUL4
21/05/2020	AZUL4	10,84	21/05/2020AZUL4
20/05/2020	AZUL4	10,31	20/05/2020AZUL4
19/05/2020	AZUL4	10,73	19/05/2020AZUL4
18/05/2020	AZUL4	10,18	18/05/2020AZUL4
17/05/2020	AZUL4	10,75	17/05/2020AZUL4
22/05/2020	GOLL4	10,71	22/05/2020GOLL4
21/05/2020	GOLL4	10,28	21/05/2020GOLL4
20/05/2020	GOLL4	10,94	20/05/2020GOLL4
19/05/2020	GOLL4	10,14	19/05/2020GOLL4
18/05/2020	GOLL4	10,83	18/05/2020GOLL4
17/05/2020	GOLL4	10,46	17/05/2020GOLL4
22/05/2020	ITUB4	10,84	22/05/2020ITUB4
21/05/2020	ITUB4	10,55	21/05/2020ITUB4
20/05/2020	ITUB4	10,89	20/05/2020ITUB4
19/05/2020	ITUB4	10,17	19/05/2020ITUB4
18/05/2020	ITUB4	10,95	18/05/2020ITUB4
17/05/2020	ITUB4	10,58	17/05/2020ITUB4

And another Table, called: Company

Table Company:
PERI	SETO	STCK2	LOPP	NDAN	KKEY
22/05/2020	TRANSPORTE	AZUL4	862,9401	79	22/05/2020AZUL4
21/05/2020	TRANSPORTE	AZUL4	529,1703	32	21/05/2020AZUL4
20/05/2020	TRANSPORTE	AZUL4	219,3461	54	20/05/2020AZUL4
19/05/2020	TRANSPORTE	AZUL4	192,0612	71	19/05/2020AZUL4
18/05/2020	TRANSPORTE	AZUL4	363,1782	71	18/05/2020AZUL4
17/05/2020	TRANSPORTE	AZUL4	511,5227	42	17/05/2020AZUL4
22/05/2020	TRANSPORTE	GOLL4	375,942	23	22/05/2020GOLL4
21/05/2020	TRANSPORTE	GOLL4	217,9886	41	21/05/2020GOLL4
20/05/2020	TRANSPORTE	GOLL4	452,4859	102	20/05/2020GOLL4
19/05/2020	TRANSPORTE	GOLL4	472,8591	128	19/05/2020GOLL4
18/05/2020	TRANSPORTE	GOLL4	468,6829	33	18/05/2020GOLL4
17/05/2020	TRANSPORTE	GOLL4	540,9126	49	17/05/2020GOLL4
22/05/2020	BANCO	ITUB4	509,4968	26	22/05/2020ITUB4
21/05/2020	BANCO	ITUB4	150,8229	22	21/05/2020ITUB4
20/05/2020	BANCO	ITUB4	258,7994	124	20/05/2020ITUB4
19/05/2020	BANCO	ITUB4	499,6926	59	19/05/2020ITUB4
18/05/2020	BANCO	ITUB4	646,8618	59	18/05/2020ITUB4
17/05/2020	BANCO	ITUB4	905,9012	38	17/05/2020ITUB4

As you can see, both tables are linked via KKEY.

My first chart shows the following equation, with STCK as Dimension:

=FirstSortedValue(VDAF, -DVPG)/(FirstSortedValue(LOPP, -PERI)/FirstSortedValue(NDAN, -PERI))

So, in this case QlikView is collecting the following values:

Table Stocks:
DVGP	STCK	VDAF	KKEY
22/05/2020	AZUL4	10,65	22/05/2020AZUL4
21/05/2020	AZUL4	10,84	21/05/2020AZUL4
20/05/2020	AZUL4	10,31	20/05/2020AZUL4
19/05/2020	AZUL4	10,73	19/05/2020AZUL4
18/05/2020	AZUL4	10,18	18/05/2020AZUL4
17/05/2020	AZUL4	10,75	17/05/2020AZUL4
22/05/2020	GOLL4	10,71	22/05/2020GOLL4
21/05/2020	GOLL4	10,28	21/05/2020GOLL4
20/05/2020	GOLL4	10,94	20/05/2020GOLL4
19/05/2020	GOLL4	10,14	19/05/2020GOLL4
18/05/2020	GOLL4	10,83	18/05/2020GOLL4
17/05/2020	GOLL4	10,46	17/05/2020GOLL4
22/05/2020	ITUB4	10,84	22/05/2020ITUB4
21/05/2020	ITUB4	10,55	21/05/2020ITUB4
20/05/2020	ITUB4	10,89	20/05/2020ITUB4
19/05/2020	ITUB4	10,17	19/05/2020ITUB4
18/05/2020	ITUB4	10,95	18/05/2020ITUB4
17/05/2020	ITUB4	10,58	17/05/2020ITUB4

Blue for AZUL4, RED for GOLL4 and GREEN for ITUB4 (the first ones)... and the result is:

AZUL4	GOLL4	ITUB4
0,974168	0,659618	0,547711

But now, what I want is... if the user selects AZUL4 for example, this company belongs to TRANSPORTE Sector (SETO Filed). So, I would like to apply the same formula but considering the average for all companies belonging to TRANSPORTE.

That would be:

Table Stocks:
DVGP	STCK	VDAF	KKEY
22/05/2020	AZUL4	10,65	22/05/2020AZUL4
22/05/2020	GOLL4	10,71	22/05/2020GOLL4

Table Company:
PERI	SETO	STCK2	LOPP	NDAN	KKEY
22/05/2020	TRANSPORTE	AZUL4	862,9401	79	22/05/2020AZUL4
22/05/2020	TRANSPORTE	GOLL4	375,942	23	22/05/2020GOLL4

As you can see, I'm not considering the ITUB4, because she is not a TRANSPORTE company.

So, I'm expecting the system to calculate the value for AZUL4 and GOL4 and then, execute the average, that will be: 

AZUL4	GOLL4	AVERAGE
0,974168	0,659618	0,816893

 I hope it's better now!

Thank you so much!

[brunolelli87]

Do you guys have any idea how to solve it?

[brunolelli87]

Any news?

🤔

[brunolelli87]

Please, find my QlikViewfile below for your reference...

This is the chart I'm working with:

Thanks for all your Help!

[brunolelli87]

This kind of problem drives me crazy!
I can't understand what's going on...

[brunolelli87 · Accepted solution]

Hello guys,

Why does the following expression is not "Not considering" the selections made in the STCK field?

=AVG(TOTAL {$<STCK=>}

AGGR(FirstSortedValue(VDAF, -DVPG), STCK, SETO) /

	(
		AGGR(FirstSortedValue(LOPP, -PERI), STCK, SETO) /
		AGGR(FirstSortedValue(NDAN, -PERI) , STCK, SETO)
	)

)

What do you guys think about it?

Thanks

[brunolelli87]

Hey, I got...

This is what I did!

=AVG( TOTAL {<STCK,SETO=p(SETO)>}

AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}VDAF, -DVPG), STCK, SETO) /

	(
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}LOPP, -PERI), STCK, SETO) /
		AGGR(FirstSortedValue({<STCK,SETO=p(SETO)>}NDAN, -PERI), STCK, SETO)
	)

)

Thanks for this Community!