Any help with the following would be great (The original problem is at the bottom and an update is above it!):
I would have thought this would work, but the AND feature does not seem to perform. If you leave the If formula in, but remove the AND part it works. As soon as the AND goes back in it will not return any codes
=NL("rows","Job Ledger Entry","Global Dimension 1 Code","Global Dimension 1 Code",$C$14,"Posting Date",$D$21,"Global Dimension 1 Code","=IF(AND(NF(,""Total Price"")<>0,NF(,""total cost"")<>0,),NF(,""Global Dimension 1 Code""),""0"")")
——————————————————————————–
From: John Richardson
Sent: 12 November 2009 12:16
To: Paul S. Wilson
Subject: JET combo fields
I am trying to filter by combining two fields, each with different filters, from the same table, but without success.
What I want is - If Total Cost <>0, AND Total Price<>0, then return Global Dim 1. ie if any income or expenditure entries have been passed to a Gl Dim 1 code, then I want the formula to return the Gl Dim 1 value. This is a simple P&L analysis.
Global Dim 1 are our contract codes. I am then using an NL formula that uses the Gl Dim 1 code to find the values posted from GL numbers ie income and direct costs.
I tried this but it wont work: -
CELL F33 =NL("rows","Job Ledger Entry","Global Dimension 1 Code","Global Dimension 1 Code",$C$11,"Posting Date",$D$21,"Global Dimension 1 Code",=AND(""Total Price"", ""total cost"")","<>0")
The reference to C11 is the Gl Dim 1 range. Posting Date D21 is also a range.
CELL F33 =NL("Sum","G/L Entry","Amount","Global Dimension 1 Code","@@"&$F33,"G/L Account No.",I$13,"Posting Date",$D$21)*-1
The reference to I13 is the GL number range.
The report also takes over three minutes to run
Any help would be much appreciated.
John
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi,
A couple of problems with your formula.
1. You have filtered to Global Dimension 1, Twice.
2. You dont need to have NF(,"Field") if there isn't any Calculation to it.
So try=NL("rows","Job Ledger Entry","Global Dimension 1 Code","Global Dimension 1 Code",$C$14,"Posting Date",$D$21,"Total Price","<>0","total cost","<>0") -
Jet Reports Historic Posts I thought that by filtering Price and Cost fields separately then the formula calculates from left to right.
As such, if I filter say, by Price first, as suggested, then I only get Price entries that also have Cost entries. I am missing the Cost entries that do not also have Price.
I want the Price values AND Cost values, ie those that have Price only, and others that have Cost only, and those that have both. Hence my attempts to combine two fields.
Or have I misunderstood the basics -
Jet Reports Historic Posts ahhh
i understand now.
instead of AND "Total Cost" <>0 and "Total Sell" <>0
you want OR "Total Cost" <>0 or "Total Sell" <>0
B2: =NL("Filter","Job Ledger Entry","Entry No.","Global Dimension 1 Code",$C$14,"Posting Date",$D$21,"Total Price","<>0")
B3: =NL("Filter","Job Ledger Entry","Entry No.","Global Dimension 1 Code",$C$14,"Posting Date",$D$21,"Total Cost","<>0")
B3: =NL("Rows","Job Ledger Entry","Global Dimension 1 Code","Entry No.",NP("Union",$B$2,$B$3))
The first two filters get a series of Entry No's
the Rows returns a lost of Global Dimension 1 Code's from those entry numbers.
a point to remember with Filters. Make sure the value you return (Entry No. in this example) is unique to that line.
i.e. if we brought back a list of Item Numbers the Rows would bring back incorrect Data
-Bromy
Please sign in to leave a comment.