sum of last available prices with set analysis and aggregation

[michal]

Hello Guys.

I have a set of data with products, prices and dates of these prices.

I want to sum prices of all products, but only last available prices.

Could you help create a working expression for that?

PRODUCT	PRICE	DATE OF PRICE
A1	11	2014-10-20
A1	10	2014-10-21
A1	9	2014-10-22
A2	12	2014-10-23
A2	13	2014-10-24
A2	14	2014-10-25

The result here should be 23: 9 (for product A1) + 14 (for product A2) = 23.

[Anonymous · Accepted solution]

Try this:

=sum(aggr(if(max(TOTAL <PRODUCT> [DATE OF PRICE])=[DATE OF PRICE], sum(PRICE)), PRODUCT, [DATE OF PRICE]))

[jaimeaguilar]

Hi,

you may resolve this with aggr and firstsortedvalue,

check the attached example,

regards

[gartigas]

I think you can use this:

FirstSortedValue (PRICE,-DATE_OF_PRICE) as LAST_PRICE,

The minus sign (before Date o f Price) is due to the fact that you want the "Last Value".

There is not a "LastSortedValue" function.

Regards

Gustavo