Return highest qty on a single day by item

I have a set of data that has, amongst others, the following fields;

  • Date of Booking (Parent Record)
  • Item (Sublist)
  • Qty on Booking (Sublist)

I want a search that is going to return the date that each item was booked the most, and how many items were booked on that day. I will even settle for the highest daily booking per item

If I create a search that is

  • Date GROUPED
  • Item GROUPED
  • Qty on Booking SUMMED

I get all the dates and the total of the item ordered on that day. A good start.

But if I change the search to be

  • Item GROUPED

Then I get the highest Qty on a Booking Line for the day, not the day with the highest Total Booked for the the daySum

In other words, if the day with the highest bookings for Widget A was 01/01/22 and the total Booking where 8 made up of

  • Booking 1 for 3
  • Booking 2 for 5

I would expect the results set to return

01/01/22 – Widget A – 8

But instead I am getting

01/01/22 – Widget A – 5 Because 5 was the highest line qty for the day.

So please help me figure out how to return a dataset that will be

This is the date that this item was booked the most and this is how many were booked.

Thank you

Beginner Asked on August 23, 2022 in Saved Searches.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor   Become a Sponsor