Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
umerikhlas
Contributor III
Contributor III

Need Help in Multiple Aggregations

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

2 Replies
stabben23
Partner - Master
Partner - Master

Hi,

have a look into Attached qvw. Maybe this could help.

sunny_talwar

You can try FirstSortedValue() Function

FirstSortedValue(Aggr(Sum(Price), SecurityID, Date), -Aggr(Max(Date), SecurityID, Date))