Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please someone could help me.
I want to list the last price in every period for all my material
Tks in advance.
Marco
I have this data:
Material | Period | Date | Price |
A | 201801 | 15/01/2018 | 5 |
A | 201801 | 20/01/2018 | 6 |
A | 201802 | 15/02/2018 | 7 |
A | 201802 | 20/02/2018 | 8 |
B | 201801 | 10/01/2018 | 20 |
B | 201801 | 25/01/2018 | 21 |
B | 201801 | 27/01/2018 | 22 |
B | 201802 | 10/02/2018 | 25 |
B | 201802 | 25/02/2018 | 24 |
B | 201802 | 27/02/2018 | 23 |
I want this result:
Material | Period | 201801 | 201802 |
A | 6 | 8 | |
B | 22 | 23 |
May be try this
Dimension
Material
Period
Expression
FirstSortedValue(Price, -Date)
May be try this
Dimension
Material
Period
Expression
FirstSortedValue(Price, -Date)
Another script based solution is
T1:
LOAD * INLINE [
Material, Period, Date, Price
A, 201801, 15/01/2018, 5
A, 201801, 20/01/2018, 6
A, 201802, 15/02/2018, 7
A, 201802, 20/02/2018, 8
B, 201801, 10/01/2018, 20
B, 201801, 25/01/2018, 21
B, 201801, 27/01/2018, 22
B, 201802, 10/02/2018, 25
B, 201802, 25/02/2018, 24
B, 201802, 27/02/2018, 23
];
Left Join(T1)
LOAD
Material,
Period,
Max(Date) as Date,
1 As IsLastPrice
Resident T1
GROUP By
Material,
Period
;
I strongly recommend Sunny's answer, however another way of doing it in the chart is using the below expression in a Straight or Pivot table along with Dimensions as Material and Period.
sum(if(Aggr(NODISTINCT Date(max(Date),'DD/MM/YYYY'),Material,Period) =Date,Price))
Hi Sunny,
many tks; it works very well.
ciao
Marco
Hi Sasidhar,
tks; a interesting alternative.
ciao
Marco
Hi Sudeep,
your note is correct; the anwer of Sunny works very well.
Also yours works very well.
ciao
Marco
Glad that you got this working.. Please close this thread by marking a correct and any helpful answers