Hi All,
I'm struggling with the old NP(Difference) function.
In the Job Ledger I find postings of resources of 2 categories with distinctive numbering (0-9999 for humans, FAC-* for machines).
I need to produce a report on manhours spent on projects without machines, so only using human resources.
So, in D5 I created a filter to find all projects with machine-hours:NL("filter";"job ledger entry";"job no.";"no.";"FAC-*";"job no.";"<>''";"Global Dimension 2 Code";"JIP|MARKT")In D6 a filter yielding a list of all job no's with manhours AND machine-hours in 2011NL("filter";"job ledger entry";"job no.";"posting date";"010111..311211";"type";"Resource")Now I want to know how much hours resource 0865 has spent on jobs NOT using machines, so in E8 is =NL("rows";"job ledger entry";"quantity";"posting date";"010111..311211";"job no.";NP("Difference";$D$6;$D$5);"no.";"0865";"Global Dimension 2 Code";"MARKT|JIP")This returns a #VALUE error saying "Empty filter not allowed".
I checked the filters, and when separately run as rows statement, they produce exactly what I expect. Just combining them in NP won't work.
Where do I go wrong?
Willem
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Willem,
Okay I see a couple problems here. First of all, your NL(Rows) formula is filtering for the job number on your NP(Difference) but then after that it's filtering by a specific number. It seems that if you're going to filter by a specific number then there's no point of finding the job numbers which meet a certain criteria by number with the NP(Difference) formula.
Secondly, I don't understand exactly why you're using NP(Difference) in the first place here. Why don't you just use a straight up filter to get the Job numbers for for humans like this:=NL("rows";"job ledger entry";"quantity";"posting date";"010111..311211";"no.";"0..9999";"Global Dimension 2 Code";"MARKT|JIP")
Does that not work for you?
Regards,
Hughes
Please sign in to leave a comment.