Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
use firstsortedvalue()
LastlastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),3)
LastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),2)
CurrentPrice =firstsortedvalue(orderprice,-aggr(orderdate,article))
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)
use firstsortedvalue()
LastlastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),3)
LastPrice =firstsortedvalue(orderprice,-aggr(orderdate,article),2)
CurrentPrice =firstsortedvalue(orderprice,-aggr(orderdate,article))
Hi
thank you, but this only works with CurrentPrice firstsortedvalue(orderprice,-aggr(orderdate,article))
the other ones arent getting any data ("-")?
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)