[dominicb]

I've got a transaction search with the following criteria:

Date is within last month

Type is Sales Order

Item : Type is Inventory Item

On the result side, I'm summarising columns:

Date Group

Item Group

Quantity Sum

This works fine and gives me the total number of any given item sold on each day.

I now need to refine this further, so it only shows the row with the highest quantity sold for any given item. Basically, we're trying to identify how much stock needs to be available in a given location in order to satisfy typical daily demand.

Can anyone shed any light on how to do this?
This is a cached copy. Click here to see the original post.

[KCSF Bud]

Any reason you couldn't just use Maximum as summary for quantity?

[dominicb]

Using maximum returns the transaction with the highest line quantity on the day and every day within the date range.

What I'm trying to do is return a range of dates, but display the sum quantity on the day with the highest total sales, ie.

20th June – Item 22222 total sales volume 12

21st June – Item 22222 total sales volume 15

22nd June – Item 222 total Sales volume 10

In the above case, return just line 2, the day with the highest sales volume.

I think it needs to be KEEP DENSE RANK but I'm struggling to understand this, being more used to standard SQL queries.

[Author]

Posts