0

Sort on the average sales by country

Jet Reports Historic Posts

September 11, 2009 10:16

None

Follow

Hi,

Maybe this has been covered somewhere else, but my searches don't come up with a solution…..

I want a report that shows the average sales per country. So far so good….
But I want it sorted by average sales. And that is something else….

I get my data from the Customer table.
Basic report shows at least 4 columns:
Country codes (cell C6): =NL("Rows";"Customer";"Country code")
Number of customers per country (cell H6): =NL("Count";"Customer";"No.";"Country code";C6)
Total sales per country (cell I6): =NL("Sum";"Customer";"Balance ($)";"Country code";C6)
Average (cell J6,excel formula): =I6/H6
That works fine, but….

I like to have the report sorted on column J, the excel formula.

When I try to implement the seperate Jet-functions in one Filterfield value I get errors.

First I tried to sort on only the number of customers by replacing C6 with:
=NL("Rows";"Customer";"Country code";"-=NL(""Count"";""Customer"";""No."";""Country code"";NF(;""Country code""))";"*")
Works like a charm!

Same for sorting on total sales, C6 will be:
=NL("Rows";"Customer";"Country code";"-=NL(""Sum"";""Customer"";""Balance ($)"";""Country code"";NF(;""Country code""))";"*")
No trouble at all.

But when I try to use both as a Filterfield value something breaks.
When C6 contains this:
=NL("Rows";"Customer";"Country code";"-=Local(NL(""Count"";""Customer"";""No."";""Country code"";NF(;""Country code""))/NL(""Sum"";""Customer"";""Balance ($)"";""Country code"";NF(;""Country code"")))";"*")
the report is sorted by country code.
Removing the Local() statement returns errors.

What am I missing? Or why won't this work?

Any help is greatly appreciated.

rmw

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts September 11, 2009 14:49

  Is it possible that you are returning a zero in your NL("Sum") formula? If so, you could be getting a division by zero error.
  
  Maybe try something like
  
  =NL("Rows";"Customer";"Country code";"-=Local(NL(""Count"";""Customer"";""No."";""Country code"";NF(;""Country code""))/(NL(""Sum"";""Customer"";""Balance ($)"";""Country code"";NF(;""Country code""))+.01))";"*")
  This will prevent a division by zero, but will make your amounts off by .01, but only for sorting purposes.
  
  Also, why are you using the "Balance ($)" field. Shouldn't you be using the "Sales ($)" field instead?

• 0
  
  Jet Reports Historic Posts September 11, 2009 15:05

  Thx for the quick reply.
  
  

  Also, why are you using the "Balance ($)" field. Shouldn't you be using the "Sales ($)" field instead?

  
  Ofcourse it should have been Sales :oops:
  And furthermore: it should have been this calculation as well:
  -=local(nl(""Sum"";""Customer"";""Sales ($)"";""Country code"";nf(;""Country code""))/nl(""Count"";""Customer"";""No."";""Country code"";nf(;""Country code"")))
  Dividing the total sales by customer count instead of the other way around :oops:
  Both changes made no difference: still sorted by country.
  
  In this report no country has zero customers, so dividing by zero is not happening. But it is something to consider, thx
  
  rmw
• 0
  
  Jet Reports Historic Posts September 11, 2009 15:25

  Hmmmm, now breaks my wooden shoe (as we say here in Holland, when something happens that we don't understand ;) )
  
  I removed the Local() indication from the formula and suddenly it all works….
  
  I am pretty sure I tried it before without it, and it gave me all errors…
  Anyway, this works now
  =nl("Rows";"Customer";"Country code";"-=nl(""Sum"";""Customer"";""Sales ($)"";""Country code"";nf(;""Country code""))/nl(""Count"";""Customer"";""No."";""Country code"";nf(;""Country code""))";"*")
  :D
  
  rmw