Need to get last purchase date, last purchase order and last purchase price for product

[Manisha1407]

Hi Team,

I need to bring the last purchase date, last purchase order and last purchase price for the list of products.

can you please help me with the set expression to get such information against each unique product code.

Regards

Manisha Tehliyani 

[vinieme12]

As below

lastPurchasedDate = Max(PurchasedDate)

lastPurchasedOrder = FirstSortedValue(OrderNo,-PurchasedDate)

lastPurchasedPrice = FirstSortedValue(Price,-PurchasedDate)

[Manisha1407]

Hi Vineeth,

When i used the expression provided by you, it is coming as Invalid dimension.

I have attached the table i am preparing  in which i want this field. 

Further i have used the below expression to get the last PO date and now i want the price used in this PO date.

=Date(Aggr(Max(PODATE),ITEM),'DD-MM-YYYY')

Please Advise.

[Gabbar]

Use it as measure not dimension.

[Manisha1407]

Hi Akash,

It worked as a measure but giving multiple values for all companies.

Please advise to get desired output.

Desired Output

Company			Stock on hand
Item	Last Puchase date	Last Price	111	112	113	114
A	15-02-2023	20.5	8	2	12	5
B	15-05-2022	19.8	10	4	14	7
C	20-01-2022	21.87	9	3	13	6

Current Output

Company		Last Purchase Price				Stock on hand
Item	Last Puchase date	111	112	113	114	111	112	113	114
A	15-02-2023	20.5	0	28	0	8	2	12	5
B	15-05-2022		2		19.8	10	4	14	7
C	20-01-2022	21.87	0	0	0	9	3	13	6

[Gabbar]

If you are using Companies as a column in the Pivot table it will automatically bifurcate Data based on companies,
If you want it just to show one column of data I think you will need to do a change in the load editor and then use it as a dimension.  @vinieme12  sir, am I right?

[vinieme12 · Accepted solution]

wrap the calculated dimensions in AGGR() as below, 

lastPurchasedDate = AGGR(Date(Max(PurchasedDate),'DD-MM-YYYY'),ITEM)

lastPurchasedOrder = AGGR(FirstSortedValue(OrderNo,-PurchasedDate),ITEM)

lastPurchasedPrice = AGGR(NUM(FirstSortedValue(Price,-PurchasedDate),'$#,###.##'),ITEM)

[Manisha1407]

Hi Vineeth,

It worked!!

thank you so much.

Regards

Manisha Tehliyani

[Gabbar]

Hey @Manisha1407  can you share the screenshot of output table.

[Manisha1407]

Sure Akash.