# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor II

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

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

Labels (2)

• ### Set Analysis

1 Solution

Accepted Solutions
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
10 Replies
Champion III

As below

lastPurchasedDate = Max(PurchasedDate)

lastPurchasedOrder = FirstSortedValue(OrderNo,-PurchasedDate)

lastPurchasedPrice = FirstSortedValue(Price,-PurchasedDate)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor II
Author

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

Partner - Creator II

Use it as measure not dimension.

Regards, Akash Dhandhi.
Contributor II
Author

Hi Akash,

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

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
Partner - Creator II

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?

Regards, Akash Dhandhi.
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Contributor II
Author

Hi Vineeth,

It worked!!

thank you so much.

Regards

Manisha Tehliyani

Partner - Creator II

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

Regards, Akash Dhandhi.