Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Using average with FirstSortedValue

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

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

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

View solution in original post

8 Replies
Highlighted

Re: Using average with FirstSortedValue

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

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

Yes, 

Of course, I can...

So, this is how my QlikView file is structured:

I have one table called Stocks:

Table Stocks:
DVGPSTCKVDAFKKEY
22/05/2020AZUL410,6522/05/2020AZUL4
21/05/2020AZUL410,8421/05/2020AZUL4
20/05/2020AZUL410,3120/05/2020AZUL4
19/05/2020AZUL410,7319/05/2020AZUL4
18/05/2020AZUL410,1818/05/2020AZUL4
17/05/2020AZUL410,7517/05/2020AZUL4
22/05/2020GOLL410,7122/05/2020GOLL4
21/05/2020GOLL410,2821/05/2020GOLL4
20/05/2020GOLL410,9420/05/2020GOLL4
19/05/2020GOLL410,1419/05/2020GOLL4
18/05/2020GOLL410,8318/05/2020GOLL4
17/05/2020GOLL410,4617/05/2020GOLL4
22/05/2020ITUB410,8422/05/2020ITUB4
21/05/2020ITUB410,5521/05/2020ITUB4
20/05/2020ITUB410,8920/05/2020ITUB4
19/05/2020ITUB410,1719/05/2020ITUB4
18/05/2020ITUB410,9518/05/2020ITUB4
17/05/2020ITUB410,5817/05/2020ITUB4

 

And another Table, called: Company

Table Company:
PERISETOSTCK2LOPPNDANKKEY
22/05/2020TRANSPORTEAZUL4862,94017922/05/2020AZUL4
21/05/2020TRANSPORTEAZUL4529,17033221/05/2020AZUL4
20/05/2020TRANSPORTEAZUL4219,34615420/05/2020AZUL4
19/05/2020TRANSPORTEAZUL4192,06127119/05/2020AZUL4
18/05/2020TRANSPORTEAZUL4363,17827118/05/2020AZUL4
17/05/2020TRANSPORTEAZUL4511,52274217/05/2020AZUL4
22/05/2020TRANSPORTEGOLL4375,9422322/05/2020GOLL4
21/05/2020TRANSPORTEGOLL4217,98864121/05/2020GOLL4
20/05/2020TRANSPORTEGOLL4452,485910220/05/2020GOLL4
19/05/2020TRANSPORTEGOLL4472,859112819/05/2020GOLL4
18/05/2020TRANSPORTEGOLL4468,68293318/05/2020GOLL4
17/05/2020TRANSPORTEGOLL4540,91264917/05/2020GOLL4
22/05/2020BANCOITUB4509,49682622/05/2020ITUB4
21/05/2020BANCOITUB4150,82292221/05/2020ITUB4
20/05/2020BANCOITUB4258,799412420/05/2020ITUB4
19/05/2020BANCOITUB4499,69265919/05/2020ITUB4
18/05/2020BANCOITUB4646,86185918/05/2020ITUB4
17/05/2020BANCOITUB4905,90123817/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:
DVGPSTCKVDAFKKEY
22/05/2020AZUL410,6522/05/2020AZUL4
21/05/2020AZUL410,8421/05/2020AZUL4
20/05/2020AZUL410,3120/05/2020AZUL4
19/05/2020AZUL410,7319/05/2020AZUL4
18/05/2020AZUL410,1818/05/2020AZUL4
17/05/2020AZUL410,7517/05/2020AZUL4
22/05/2020GOLL410,7122/05/2020GOLL4
21/05/2020GOLL410,2821/05/2020GOLL4
20/05/2020GOLL410,9420/05/2020GOLL4
19/05/2020GOLL410,1419/05/2020GOLL4
18/05/2020GOLL410,8318/05/2020GOLL4
17/05/2020GOLL410,4617/05/2020GOLL4
22/05/2020ITUB410,8422/05/2020ITUB4
21/05/2020ITUB410,5521/05/2020ITUB4
20/05/2020ITUB410,8920/05/2020ITUB4
19/05/2020ITUB410,1719/05/2020ITUB4
18/05/2020ITUB410,9518/05/2020ITUB4
17/05/2020ITUB410,5817/05/2020ITUB4

 

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

AZUL4GOLL4ITUB4
0,9741680,6596180,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:
DVGPSTCKVDAFKKEY
22/05/2020AZUL410,6522/05/2020AZUL4
22/05/2020GOLL410,7122/05/2020GOLL4

 

Table Company:
PERISETOSTCK2LOPPNDANKKEY
22/05/2020TRANSPORTEAZUL4862,94017922/05/2020AZUL4
22/05/2020TRANSPORTEGOLL4375,9422322/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: 

AZUL4GOLL4AVERAGE
0,9741680,6596180,816893


 I hope it's better now!

Thank you so much!

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

Do you guys have any idea how to solve it?

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

Any news?

🤔

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

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

This is the chart I'm working with:

Chart.png

Thanks for all your Help!

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

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

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Using average with FirstSortedValue

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!