set analysis with min(date) by dimension

[wunderch]

Hi guys,

I have the following table:

product	date	price
A	01.01.2018	100
A	01.01.2019	120
A	01.01.2020	150
A	01.01.2021	190
A	01.01.2022	250
A	01.01.2023	300
B	01.01.2020	400
B	01.01.2021	470
B	01.01.2022	500
B	01.01.2023	600

So my challenge is, when I select date from 01.01.2019 to 01.01.2022 I want get the following table

product	Startprice	Endprice
A	120	250
B	400	500

If a price on Startdate is available then take this price, if no price is available then take the first price what is in the selected date range.

I think the formula must be something like this:

aggr(only({<date={aggr(min(date), product)}>} price),  product)

Thanks for your help!

Regards 

Chris

[joao_renato · Accepted solution]

Olá,
Testa as seguintes expressões:

Startprice: Sum(If(Aggr(nodistinct Min(date),product) = date, price))
Endprice: Sum(If(Aggr(nodistinct Max(date),product) = date, price))

[wunderch]

Olá Joao,

Muchas gracias, esa es exactamenta la solución. 

Que tenga un lindo día.

Chris