0

Sum of Top 10 Sales for Each Day

Leo Lee

April 29, 2019 11:13

Answered

Follow

Hello

I want to obtain daily top 10 sales data to plot a chart with to get a trend over particular months.  I tried starting off by attempting to sum one day's top 10 sales but have already fallen over the first hurdle.  My formula looks like this:

=NL("Sum","Sales Invoice Line","Amount","Posting Date","01/03/19","-Amount","*","Limit=","10")

This creates an error:

"Specifying a limit is not allowed when performing a sum."

Is it therefore actually possible to calculate the sum of the largest 10 sales each day?  Following on from that, is it also then possible to do this for each day over the course of a month? I.e. Sum of top 10 for 1st, Sum of top 10 for 2nd and so forth?

Many thanks in advance for any assistance.

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis April 29, 2019 15:59

  Hello Leo -

   

  Of course, the easiest (and maybe fastest running) way to do this would be to simply list out the top 10 amounts for each of my dates and then total those.

  Something similar to this where I have listed a top 5 for each of my dates (I'm only showing 3 dates to keep it simple):

  

  
  Because I'm hiding the details, my results look like this:

  

   

  If I were absolutely set against listing the underlying details, I can still do it.  The calculation requires a little more thought.   I would need to find my 6th largest Amount for that day and then total up anything larger than that.

  Something like this:

  

  Here's that function:

  =NL("Sum","Cust. Ledger Entry","Amount","Posting Date",D4,"Amount","="">""&NL(6,""Cust. Ledger Entry"",""Amount"",""-Amount"",""*"",""Posting Date"","""&D4&""",""Document Type"",""Invoice"")")

   

  When I run this:

  

   

  I hope that helps.

• 0
  
  Leo Lee April 30, 2019 11:13

  Hi Harry

  Your NL("Sum"... solution was exactly what I was looking for.  The listing and hiding records solution was also very useful for me to do performance benchmarking and I will revert to that if the sum function starts taking too long.

  Many thanks