Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Aggr() function in set analysis

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!

1 Solution

Accepted Solutions
MVP

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

8 Replies
MVP

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])

MVP

Try

FirstSortedValue( Price, -[Order Date])

Create Straight Table

Dimension:

OrderID

PartID

Part Description

Expression:

=FirstSortedValue(Price ,- Order Date)

MVP

just try with this

firststoredvalue(price,-[order date])

MVP

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

Not applicable
Author

Thanks all for your help it worked perfectly fine

Not applicable
Author

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.

MVP

Great,