Hi,
I'm trying to use a Union function within an NL Rows command with no success.
I want to create a report which looks at both the Sales Header and Sales Invoice Header to return customers (Rows) which have not placed an order within the last 3 months. Currently I have the following which returns the customer list in full and does not filter out those customers who have ordered in the last 3 months.
=NL("Rows","Customer",,NL("Last",NP("Union",NL("Last","Sales Header","No.","Sell-to Customer No.","No.","Order Date",NP("DateFilter",,NP("Eval","=B2"))),NL("Last","Sales Invoice Header","Order No.","Sell-to Customer No.","No.","Order Date",NP("DateFilter",,NP("Eval","=B2"))))))
Can anybody help?
Thanks.
Date
Votes
13 comments
-
Jet Reports Historic Posts Official comment Ian,
Ah, yes I thought that might be the problem, which is why I highlighted it. You probably have to do something like this:E4 : Contains a date range calculated based on a formula E15 : =NL("Rows", NP("Difference",$D8,NP("Union",$D9,$D10))) D8 : =NL("Filter","18 Customer","No.") D9 : =NL("Filter","Customer","No.","Link=Customer","Sales Header","Sell-to Customer No.","=No.","Order Date",$E$4) D10: =NL("Filter","Customer","No.","Link=Customer","Sales Invoice Header","Sell-to Customer No.","=No.","Order Date",$E$4)
This involves a union once again, but hopefully it's faster than the previous effort since it's unioning from the Customer table rather than from the sales tables directly. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Well your first problem is that you are trying to use NL(Last) within your NP(Union) formulas which won't do much (only 2 records will get returned, the last record from the first formula and the last record from the 2nd formula). You need to use NL(Filter) for this. Also, the way you have this embedded inside the NL(Rows) is wrong. Try something like this:=NL("Rows",NP("Union",NL("Filter","Sales Header","No.","Sell-to Customer No.","No.","Order Date",NP("DateFilter",,NP("Eval","=B2"))),NL("Filter","Sales Invoice Header","Order No.","Sell-to Customer No.","No.","Order Date",NP("DateFilter",,NP("Eval","=B2")))))
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Thanks for the response, it seems to have got me moving in the right direction, although I'm not quite there yet.
I now have the following:
E4 : Contains a date range calculated based on a formula
E15 : =NL("Rows", NP("Difference",$D8,$D9))
D8 : =NL("Filter","18 Customer","No.")
D9 : =NL("Filter","Customer","No.","Link=Customer","Sales Header","Sell-to Customer No.","=No.","Order Date",$E$4,,,"Link=Customer","Sales Invoice Header","Sell-to Customer No.","=No.","Order Date",$E$4)
Now it is returning doing a comparison between the full customer list and the filtered customer list (D9) but the filtered list is not seemingly finding the right data. It not pulling back the last order from either of the 2 linked tables. I imagine this is due to the missing union but if I include the union the report takes forever to run (20 mins +) because the customer list is over 3,000 records. The above approach takes 1m 30s which is fine.
I am not averse to a re-write of the report if there is a better way of getting this data.
Thanks again. -
Jet Reports Historic Posts Hi,
I'm not sure I understand what you mean by "not pulling back the last order from either of the 2 linked tables." How do you know whether it's pulling back the last order? Is there a customer being included in your list which only has an order on the last date in both tables? Maybe you could explain more what you mean by this.
Regards,
Hughes -
Jet Reports Historic Posts Hi,
When the NP Difference function runs, it currently returns customers who have placed orders within the date range specified. Assuming my understanding of the Difference function is correct, then the list which filters for customers who have placed orders in that date range should be being identified by the formula in D9 (or that is what it is supposed to be doing), then the difference should exclude them when comparing them to the full customer list.
Does that help?
Thanks,
Ian -
Jet Reports Historic Posts Ian,
So I'm not really sure if your understanding is correct because you seem to be saying 2 different things.When the NP Difference function runs, it currently returns customers who have placed orders within the date range specified.
This is the part that is incorrect. Your formulas will actually return customers who have NOT placed orders within the date range specified in E4. It takes the full list of customers and removes from it the customers with an entry in both the Sales Header and Sales Invoice Header in the specified date range. Note that a customer must have an entry in both Sales Header and Sales Invoice Header in order to be excluded from the overall list. If the customer is only in one table but not the other, they will not be excluded from the list. Does this describe your understanding of these formulas?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks again for your help on this one.
This is exactly what want it to do:Your formulas will actually return customers who have NOT placed orders within the date range specified in E4.
However, the fact that the filters require entries in both, is what I have done wrong then (I didn't realise this was the case). Is there anyway of making the formula so that it doesn't have to have an entry in both?
Thanks,
Ian -
Jet Reports Historic Posts Hi Hughes,
That has worked perfectly in returning the correct Customer No.s for the NL Rows function and was certainly quick enough :D
Could you give me a pointer as to the best way to then display the last sales order no. and last sales order date for each customer it has returned as a column on each row? I'm currently trying to run this as a union but obviously this causes significant performance issues again. I'm sure there's a better way but I'm a total Jet Reports novice (obviously) so any pointers would be great.
Thanks,
Ian -
Jet Reports Historic Posts Ian,
It seems like you could just do this as an NL(Last) function. I'm not sure about the exact tables/fields in question, but I think it would look something like this:F15: =NL("Last","Sales Invoice Header",,"Sell-to Customer No.",$E15,"+Order Date","*") G15: =NF($F15,"Order No.") H15: =NF($F15,"Order Date")
Does something like that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
All working well now.
Thanks again for all of your help!
Ian -
Jet Reports Historic Posts Hello all,
I am trying to pull rows from accounts in a specific account range for only the accounts with entries in a specific posting date range for two companies (in order to consolidate).
I have the following:
B4: =nl("rows",NP("Union",E7,E8,))
E7: =nl("ROWS","G/L Account",,"Company=",C2,"No.",">39999&<60000","Link=","g/L Entry","Posting Date","01/01/2014..12/31/2014")
E8: =nl("ROWS","G/L Account",,"Company=",D2,"No.",">39999&<60000","Link=","g/L Entry","Posting Date","01/01/2014..12/31/2014")
E7 performs correctly and pulls a number of accounts. E8 also pulls a number of accounts. The union of the two in B4 only returns the first two accounts pulled in the other two. I see previous posts that the NP needs to be nested in an NL(Rows) function. Am I not doing this properly?
Thanks in advance,
E
Jet 2012 R2 - 12.5.12353.0 (64-Bit) -
Jet Reports Historic Posts Hi,
You're almost there. You just need to change your functions in E7 and E8 to use NL(Filter) instead of NL(Rows) and specify the No. field like this:
E7: =nl("Filter","G/L Account","No.","Company=",C2,"No.",">39999&<60000","Link=","g/L Entry","Posting Date","01/01/2014..12/31/2014")
E8: =nl("Filter","G/L Account","No.","Company=",D2,"No.",">39999&<60000","Link=","g/L Entry","Posting Date","01/01/2014..12/31/2014")
Then your function in B4 should work to replicate all the account numbers.
Regards,
Hughes -
Jet Reports Historic Posts Thank you so much, Hughes, for the response. Is there any way to return the a key as opposed to just the account number field?
Please sign in to leave a comment.