Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Would you be able to explain this by some sample data and the output you expect to see from it?
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!
Do you guys have any idea how to solve it?
Any news?
🤔
Please, find my QlikViewfile below for your reference...
This is the chart I'm working with:
Thanks for all your Help!
This kind of problem drives me crazy!
I can't understand what's going on...
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
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!