Hello, I have an issue here.
I am looking to pull information, but it could reside in one of two companies.
Here is what I have done.
This will Filter the Document No. from each company.
In Cell C31 =NL("Filter","Cust. Ledger Entry","Document No.","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$T$4,"Company=","Company #1")
In Cell C32 =NL("Filter","Cust. Ledger Entry","Document No.","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$T$4,"Company=","Company #2")
The function below will bring back Document No.'s from each company.
In Cell C33 =NL("rows",NP("Union",$C$31,$D$31))
I now have the document no.'s, but now I need the posting date and amount.
I tried this, but it doesn't seem to work.
=IF(NL(,"Cust. Ledger Entry","Posting Date","Document No.",$C33,"Company=","Company #1"),NL(,"Cust. Ledger Entry","Posting Date","Document No.",$J31,"Company=","Company #2"))
Can someone help me? Thanks.
Date
Votes
7 comments
-
Jet Reports Historic Posts Could you do something like this?
=IF(NL(,"Cust. Ledger Entry","Posting Date","Document No.",$C33,"Company=","Company #1")="",NL(,"Cust. Ledger Entry","Posting Date","Document No.",$J31,"Company=","Company #2"),NL(,"Cust. Ledger Entry","Posting Date","Document No.",$C33,"Company=","Company #1"))
Regards,
Hughes -
Jet Reports Historic Posts That works…now one other thing, how do I get it to sort by posting date?
It is looking at posting dates in two different companies, how can sort them as one, from oldest date to newest ? -
Jet Reports Historic Posts By the way, thank you Hughes for helping me with my first question :)
-
Jet Reports Historic Posts No problem. I think to sort the values by posting date, you will have to replicate the posting dates first. So you would want something like this:
In Cell C31 =NL("Filter","Cust. Ledger Entry","Posting Date","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$T$4,"Company=","Company #1")
In Cell C32 =NL("Filter","Cust. Ledger Entry","Posting Date","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$T$4,"Company=","Company #2")
In Cell C33 =NL("rows",NP("Union",$C$31,$D$31))
In Cell D33 =NL("Filter","Cust. Ledger Entry","Document No.","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$C33,"Company=","Company #1")
In Cell E33 =NL("Filter","Cust. Ledger Entry","Document No.","Document No.","MT*|AT*","Global Dimension 2 Code",$E$3,"+Posting Date",$C33,"Company=","Company #2")
In Cell F33 =NL("rows",NP("Union",$D33,$E33))
So then you're replicating the unique list of sorted dates from the 2 companies and for each date you're replicating the list of Document No.'s for that date from both companies. And of course you could hide columns D and E. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts That works…now one other thing, how do I get it to sort by posting date?
It is looking at posting dates in two different companies, how can sort them as one, from oldest date to newest ?
Why not just add filter +PostingDate,* to your line thats bringing back the date ?
=IF(NL(,"Cust. Ledger Entry","Posting Date","Document No.",$C33,"Company=","Company #1","+Posting Date","*")="",NL(,"Cust. Ledger Entry","Posting Date","Document No.",$J31,"Company=","Company #2"),NL(,"Cust. Ledger Entry","Posting Date","Document No.",$C33,"Company=","Company #1","Posting Date","*")) -
Jet Reports Historic Posts No, this doesn't work…It will sort the dates in the one company, and then sort the dates in the second company.
So it will look like, Jan. Feb. Mar. April……..Jan. Feb. Mar.
I need it to be Jan. Feb. Mar. April
I need to sort them all together. -
Jet Reports Historic Posts Hughes, your suggestion worked !!
Setting the first 2 NL Filter to pull back the posting date so it automatically sort by this, and then having the second 2 NL Filter's reference the first one worked.
Man, all this work just to get a report to sort properly :)
Thanks again !!
Please sign in to leave a comment.