Need Help in Multiple Aggregations

umerikhlas — Thu, 19 Jan 2017 09:31:43 GMT

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

Re: Need Help in Multiple Aggregations

stabben23 — Thu, 19 Jan 2017 10:05:57 GMT

Hi,

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

Re: Need Help in Multiple Aggregations

sunny_talwar — Thu, 19 Jan 2017 10:27:27 GMT

You can try FirstSortedValue() Function

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

topic Need Help in Multiple Aggregations in QlikView

Need Help in Multiple Aggregations

Re: Need Help in Multiple Aggregations

Re: Need Help in Multiple Aggregations