Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Manisha1407
Contributor III
Contributor III

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

Accepted Solutions
vinieme12
Champion III
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.

View solution in original post

10 Replies
vinieme12
Champion III
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.
Manisha1407
Contributor III
Contributor III
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')

 

Please Advise.

 

 

Gabbar
Specialist
Specialist

Use it as measure not dimension.

Manisha1407
Contributor III
Contributor III
Author

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

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
Champion III
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.
Manisha1407
Contributor III
Contributor III
Author

Hi Vineeth,

 

It worked!!

 

thank you so much.

 

Regards

Manisha Tehliyani

Gabbar
Specialist
Specialist

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

Manisha1407
Contributor III
Contributor III
Author

Sure Akash.

Manisha1407_0-1679290022941.png