Hi,
I am facing an issue for which i have searched over the Qlik community but unfortunately couldn't find any satisfying answer. I have a case where i have made a virtual table using AGGR function i want to use that virtual table for another AGGR function can it be possible in QlikView expression level?
I have a case where i have to find the latest date of different securities so i made a simple AGGR function( Aggr( Max(Date),SecurityID) ) and that worked perfectly fine but i have to find the Price of Securities on latest date let me be more clear about
SecurityID Date Price
1 1/11/2016 200
2 4/12/2016 400
3 1/11/2016 600
4 1/11/2016 800
1 5/1/2017 1000
2 3/1/2017 1200
3 1/1/2017 1400
4 2/1/2017 1600
I want to find Price of SecurityID on latest date which is the last 4 rows. I have an expression define above working perfectly fine in retrieving the latest date of securities but i need to use that virtual table ( AGGR ) into another AGGR function which retrieve the price of securities at latest date. It would be something like
Aggr( Price , Aggr(Max(Date),SecurityID)
I have to show it on TextBox.
I have tried it but this is not working. That is what i think the way to retrieve it but if there is any other way please feel free to share
Thanks
UI
Hi,
have a look into Attached qvw. Maybe this could help.
You can try FirstSortedValue() Function
FirstSortedValue(Aggr(Sum(Price), SecurityID, Date), -Aggr(Max(Date), SecurityID, Date))