last price vs latest price of a product

[sayanpwc]

Hello team,

I need to show a chart with product as a dimension and two expression of latest price of the product and last price of the same product.

For ex:

Product , date,price

A, 1/9/2018,20

A,1/8/2018,20

A,1/7/2018,10

A,1/6/2018,5

So in my chart for product A latest price will be 20 and last price will be 10 . So last price means last changed price from the latest price.

Can you please help me on this?

[vamsee]

Are you looking for something like this

Source:
LOAD

Text(Product) as Product,
Date(Date#(date, 'MM/DD/YYYY')) as Date_Check,
NUM(price) as Price;

LOAD * Inline [

Product , date,price

A, 1/9/2018,20

A,1/8/2018,20

A,1/7/2018,10

A,1/6/2018,5

];

Temp:
Left Join (Source)
LOAD
Product,
(Max(Price)) as Latest_Price,
(Max(Price,2)) as Last_Price
Resident Source
Group By Product;

[sunny_talwar]

On the front end you can try this

latest price

FirstSortedValue(price, -date)

Last price

=FirstSortedValue(Aggr(If(price <> FirstSortedValue(TOTAL <Product> price, -date), price), price, Product), -Aggr(If(price <> FirstSortedValue(TOTAL <Product> price, -date), date), price, Product))

[Rafmat]

I'm afraid that Max function will not return neither the latest nor the last price, only the Largest and second largest price ... to get latest price you need to introduce order by descending date and the same for the last price as soon as price is different ...