Selecting last 3 Dates

[fatihu]

Hello Community,

I try to build a table with some specific structure and maybe someone can help me a little...

I have the following data in my "orders"-table:

article	orderDate (german date)	orderPrice
A1	14.03.2023	29€
A1	22.02.2023	30€
A1	18.01.2023	11€
A1	17.01.2023	22€
A1	01.01.2023	90€

Now I want to build the following table as "price development of last 3 orders of each article":

article	LastLastOrderPrice	LastOrderPrice	CurrentOrderPrice	Diff last 2 Orders
A1	11€	30€	29€	-1€
B1	...	...	...
C1	...	...	...

The columns "CurrentOrderPrice", "LastOrderPrice", and "LastLastOrderPrice" are not fix dates for every article. Each article has its own order-dates. Therefore, I need a possibility to say something like:

CurrentOrderPrice = take orderPrice from latest orderDate for each article

LastOrderPrice = take orderPrice from latest orderDate -1 for each article

LastLastOrderPrice = take orderPrice form latest orderDate -2 for each article

Can someone help me please?

Best Regards!

Fatih

[vinieme12 · Accepted solution]

use firstsortedvalue()

LastlastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),3)

LastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),2)

CurrentPrice =firstsortedvalue(orderprice,-aggr(orderdate,article))

[fatihu]

Hi

thank you, but this only works with CurrentPrice firstsortedvalue(orderprice,-aggr(orderdate,article))

the other ones arent getting any data ("-")?

[marcus_sommer · Accepted solution]

You may try it without the aggr() like:

LastPrice = firstsortedvalue(orderprice,-orderdate,2)

IMO better would be to transfer this task into the data-model, for example with something:

t: load distinct article, orderdate, if(article = previous(article), peek('nr') + 1, 1) as nr
resident Source order by article, orderdate desc;

and then you may use an expression like:

avg({< nr = {2}>} orderprice)