Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
umerikhlas
New 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
Honored Contributor

Re: Need Help in Multiple Aggregations

Hi,

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

Re: Need Help in Multiple Aggregations

You can try FirstSortedValue() Function

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