Hi,
I have a report that lists all lines from the sales invoice line with "posting date" and "external document no." from the sales invoice header as filters:
B5 = NL("rows","sales invoice line",,"unit price","<>0","type","item","quantity","<>0","document no.",nl("filter","sales invoice header","no.","external document no.","XXX","posting date",np("datefilter","$B$2","$B$3")))
and on one of the columns, I have the salesperson code attached to each combination of item commission group code/sell-to customer no./shipment date (salesperson could be switched in the middle of the month):
C5 = NL(,"salesperson commission rate","salesperson code","commission type","contact","no.",NF(B5,"sell-to customer no."),"item commission group code",NF(B5,"item commission group code"),"start date",CONCATENATE("''|",np("datefilter",,NF(B5,"shipment date"))),"end date",CONCATENATE("''|",np("datefilter",NF(B5,"shipment date"),)))
I know that there is a field on the sales invoice line for salesperson code (summary salesperson code), but for some reason I cannot use that field. It works fine anyway, using the above formula.
Is there a way to get a list of unique "salesperson code" based on the following 3 tables with Jetreports formula:
Salesperson commission rate:
"commission type" = "contact"
"item commission group code" = "item commission group code" from sales invoice line
"start date" <= "shipment date" from sales invoice line or blank
"end date" >= "shipment date" from sales invoice line or blank
Sales invoice header:
"external document no." = "XXX"
"posting date" = np("datefilter","$B$2","$B$3")
Sales invoice line:
"document no." = "no." from sales invoice header
"unit price","<>0","type","item","quantity","<>0"
Thank you.
Date
Votes
3 comments
-
Jet Reports Historic Posts If I follow this you have situation where there are multiple salespeople possible on a single invoice ? Not sure how thats possible but
To get header salesperson
B5 = NL("rows","sales invoice Header" filtered as needed) B6 ( NF(B5,No)) B7=B6 B10(NF(B5,salesperson Code)
c7=B7 C8 = NL("rows","sales invoice line",,"document no.",C7) C9(NF(C8,shiptocustomer). C10("first",Customer, Salespersoncode,"No.",C9)
I'm guessing that if invoice can be sold to one customer bill to another and ship to another and the salespersom is differetn at each site ?) -
Jet Reports Historic Posts Our items are divided into 2 main division with 2 subdivision each.
Each customer is attached to 1 salesperson for each subdivision.
On one invoice, there only be items from the same division but could be from different subdivision.
so, on one invoice, there is a chance that some lines belong to one salesperson and some others belong to another.
Back to my question:
My report is running perfectly. It has the detail sales information on the second sheet which I generate using the above jetreports formulas.
On the first sheet, I have the summary of sales by salesperson which I created using VBA based on the detail sheet.
I could get rid of the VBA stuff if I could generate a list of unique salesperson on the summary sheet with all the above criteria using jetreports formulas.
I wish I could use the following formulas:
NL("rows","salesperson commission rate","salesperson code","commission type","contact","link=","sales invoice line","sell-to customer no.","=no.",
"item commission group code","=item commission group code",
"shipment date",if("start date" from the main table = '',"*",np("datefilter","start date",)),
"shipment date",if("end date" from the main table = '',"*",np("datefilter",,"end date")),
"link=","sales invoice header","no.","=document no.","external document no.","XXX","posting date",np("datefilter",$B$2,$B$3))
OR
get a list from the second sheet
NL("rows","salesperson/purchaser code","code","code",sheet2!B2:B3)
I could use union, join, split to get the range on the second sheet as "123|234|345"
but since the number of rows on the second sheet is more than a thousand, it gives me error.
Is there a way to filter a query from a long excel range?
Thanks -
Jet Reports Historic Posts Now it starts to make sense
On your salesman summary sheet how about creating a filter 1 for all unique commision groups in invoice line in your time frame
Create filter 2 for customers using either the header or detail
Generate a list ( actually filter 3) of all salespeople with commission in that period using above two as filter on table that holds data Presuming you have table that has customer, item commission group and salesperson in it
Use base salesperson table to give you a row for each salesperson in filter 3 Then do a vlookup on your detail sheet Hardcode range $F$5;$H$60000
Please sign in to leave a comment.