How to show one row of total quantity for item codes in a list report

[cognovice]

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.

[Corrigon]

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

Code Select Expand

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?

[cognovice]

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.