I am looking for some suggestions on how to write an efficient formula to pull 'item' sales, filtered for a specific 'Territory' (set up as a dimension value code). So it will need to pull and summarize by item the sales for a territory by item. I can get my report to run and work flawlessly for all of the items that relate to the first customer that is associated with the territory and then also pull sales for all of those items for the other customers in the territory, however if the other customers have additional items associated with them, it is not pulling them. If I open the filter to pull all items it takes forever to run. Any suggestions? I have included the formula string below.
to filter customers, for the territory: (result shows up in $K26)
=nl("rows=2","customer",,"link=","default dimension","no.","=no.","table id","18","dimension code","territory","dimension value code",$J$3,"no.",$J$4)
To create a filter for the items: (this formula resides in C26)
=nl("filter","sales invoice line","no.","sell-to customer no.",K26,"type","item")
To pull items based on filter above (result resides in $I24 and replicates down $H)
=nl("rows=6","item","No.","no.",C26)
to pull the item information needed (this is for quantity, and I have it for amount):($H26 is a replication of the result from formula above for items)
=nl("sum","sales invoice line","std. package quantity","no.","@@"&$H26,"sell-to customer no.",$K26,"posting date",$O$10,"Std. Package Unit of Meas Code","LBS")
Thank you in advance for any and all suggestions.
Date
Votes
8 comments
-
Jet Reports Historic Posts Official comment Gary,
The reason it's not working is that you didn't copy my formula exactly. You've added the filter for Sell-to customer no. back to the Sales Invoice Line table in the link. You have this in cell I24:=nl("rows=6","item","No.","Link=","Sales Invoice Line","No.","=No.","Sell-to Customer No.",$K26,"Type","Item","Link=","Customer","No.","=Sell-to Customer No.","Territory",$J$3)
You need to have this:=nl("rows=6","item","No.","Link=","Sales Invoice Line","No.","=No.","Type","Item","Link=","Customer","No.","=Sell-to Customer No.","Territory",$J$3)
If you filter that link for 1 specific sell-to customer, you will only get the items with sales to that one specific customer. If you want all items with sales within the territory, then you can't filter it down to one specific customer. It seems like you want all items with sales within the territory, right? Or am I misinterpretting what you're saying?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
So my first comment has nothing to do with performance, but in Jet you can filter directly on a dimension and Jet does the link under the hood. So you could change your first formula to this and it should work the same way:=nl("rows=2","customer",,"Territory",$J$3)
As far as improving performance, you should replace your NL(Filter) with a Link=. So you eliminate the formula in C26 entirely and then in I24 you have this:=nl("rows=6","item","No.","Link=","Sales Invoice Line","No.","=No.","Sell-to Customer No.",K26,"Type","Item")
Link= should perform much better than NL(Filter) does. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Thank you for the suggestions. I did implement your suggestions and the performance does seem to be a little better, but I am still having the issue of the report pulling all the items for the first customer, and then pulling the info from those items for the other customers in the territory. It is however ignoring any additional items that the other customers in the territory may have sales for. Any suggestions for that? I have attached the design version of the file for you to see the layout I have used.
Thank you,
Gary
product Sales Analysis by Item test5.xlsx -
Jet Reports Historic Posts Hi Gary,
Okay your report confused me slightly because you are using the filter "For Territory" for salesperson codes instead of territories. However, I think the problem is that you're trying to filter from the outer replicator in I24 into the inner replicator inside it which will not work because the outer replicator gets expanded before the inner replicator and the expansion happens only once. So I think maybe you could solve this problem by just adding an additional level of linking in the outer replicator something like this:=nl("rows=6","item","No.","Link=","Sales Invoice Line","No.","=No.","Type","Item","Link=","Customer","No.","=Sell-to Customer No.","Salesperson Code",$J$3)
This formula is linking from the Item to the Sales Invoice Line and then from the Sales Invoice Line to the Customer in order order to filter by the Salesperson Code of the customer in the outer replicator. So essentially we're saying we want to see all Items which have sales where the customer's salesperson was X. Of course you could change Salesperson Code here to Territory if you wanted to filter by the Territory dimension instead. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Thank you for the input and suggestions. I inserted your formula suggestion, but unfortunately the report is still only pulling the items for the first customer and applying to the other customers. Any additional suggestions to get it to expand.
(Also, you were right on the salesperson code. The version that I attached was one I was testing with to try and get the results, and I substituted salesperson code for Territory to try to achieve the results needed. I truly do need the Territory")
Thank you in advance,
Gary -
Jet Reports Historic Posts Gary,
Okay so attach for me the latest version of your report with my formula inserted in design mode so I can know I'm looking at the latest things you're looking at.
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
Here you go. I have highlighted in yellow the cells that contain NL formulas.
Thank you,
Gary
product Sales Analysis by Item test5.xlsx -
Jet Reports Historic Posts Hughes,
Thank you for your help on this. It does work great now that I added your formula exactly. Currently testing to make sure works for me across all territories, but I don't see why it would not.
Thank you again,
Gary
Please sign in to leave a comment.