Hi,
Could anyone please assist. I have a list report, where I need to show the last date sold for item codes with totalling of quantity for multiple warehouses.
Item code Total Qty on Hand Last Sale Date
The data stored for last sale date and quantity on hand is by each warehouse, where as in my report, I need to total the quantity on hand show in one row along with last sale date.
I have managed to get last sale date by using the filter [last sale date]=maximum([last sale date] for item code]).
I can't do the same for quantity on hand using Total as it only shows quantity on hand for the warehouse where the latest last sale date is.
Hope the above request is clear.
Hi - I *think* I understand your request but apologies if I am off track.
My interpretation is that you want to see the following in a list (text in caps is my emphasis):
Item Codes || Total Quantity on Hand (ACROSS ALL WAREHOUSES) || Last Sale Date
Is that right?
1) If so, copy your existing query (so you have Q1 and Q2). The remove 'Last Sale Date' from Q1.
2) Apply the maximum filter to the Last Sale Date in Q2
3) Join Q2 to Q1 using Item Code as your join Key. Check the cardinality at this point - is it possible for an Item Code to NOT have a Last Sale Date? If so, does it need to show on the report? You can change the join from inner to outer here as required.
4) In the joined query, drag in the contents from Q1. Then drag in 'Last Sale Date' from Q2.
Create your list with the 3 columns. Set the Aggregate specification for Quantity to 'Total'.
How does that look?
Hi Corrigon,
First of all, thank you so much for your reply. I have just comeback from holidays and couldn't reply to your post on time. Apologies.
I will try your suggestion and see how it goes.
Thank you once again.