Aggr() function in set analysis

[#1]

Hi all,

I am using the aggr() function for the very first time and is getting a bit confused.

I have the following table:

As it can be seen from the table, the price for a part varies from one order to another (ie. from order data to another order date).

Basically I want to perform a GROUP BY on the part field to retrieve the price for each part where the Order date is the latest one. In short I want a straight table with just the blue records (Order IDs 1, 4, 6, 9).

Till now I managed to get the latest order for each part by using the following expression:

=Date(aggr( Max([Order Date]), [Part ID])) 

However, I am finding it a problem in getting the corresponding price. I tried to used the aggr() expression above in the following way:

=only({< [Order Date]={'$(=Date(aggr( Max([Order Date]), [Part ID])))'}>} Price) 

However this is not returning the desired result.

Can anyone suggest how this can be done?

Thanks!

[sunny_talwar]

Try this expression:

1) Date(Max([Order Date]))

2) FirstSortedValue(Price, -[Order Date])

Dimensions:

1) [Order ID]

2) [Part ID]

3) [Part Desc]

UPDATE: I think you don't need [Order ID] as a dimension. If you need to know that information you can add it like an expression:

FirstSortedValue([Order ID], -[Order Date])

[swuehl]

Try

FirstSortedValue( Price, -[Order Date])

[Kushal_Chawda]

Create Straight Table

Dimension:

OrderID

PartID

Part Description

Expression:

=FirstSortedValue(Price ,- Order Date)

[Chanty4u]

just try with this

firststoredvalue(price,-[order date])

[sunny_talwar · Accepted solution]

Sample attached

NOTE: In future paste your data as text instead of image so that creating a sample is easier for the people who are trying to help.

Thanks,

Sunny

[#7]

Thanks all for your help it worked perfectly fine

[#8]

Thanks Sunny for providing the QVW with an explanation. You're right next time I will but the data as text rather than an image.

Thanks again.

[sunny_talwar]

Great,

Please close the thread down by marking correct and helpful answers.

Thanks,

Sunny