Last price in a period

[eptaqlik]

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

[sunny_talwar · Accepted solution]

May be try this

Dimension

Material

Period

Expression

FirstSortedValue(Price, -Date)

[sasiparupudi1]

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

;

[sudeepkm]

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))

[eptaqlik]

Hi Sunny,

many tks; it works very well.

ciao

Marco

[eptaqlik]

Hi Sasidhar,

tks; a interesting alternative.

ciao

Marco

[eptaqlik]

Hi Sudeep,

your note is correct; the anwer of Sunny works very well.

Also yours works very well.

ciao

Marco

[sasiparupudi1]

Glad that you got this working.. Please close this thread by marking a correct and  any helpful answers