0

is there an efficient filter to find sales current vs prior

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.

8 comments

Please sign in to leave a comment.