0

filter in filter

Jet Reports Historic Posts

July 16, 2012 12:23

None

Follow

Hi,

You say you're using CountUnique but you don't mention what field you are counting unique values for. Possibly you should be counting unique dates since you're filtering for a specific customer and want to know the count of unique dates within your specified date range? So the formula might be:

=NL("CountUnique","Transportzeile","Date","Customer","11111","Date","03.05.2012..04.05.2012")
Does that work for you?

Regards,
Hughes

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts July 17, 2012 15:44 Official comment

  Aaah I see so you want to count the unique combinations of customer and date. I think I get it. You could do that with a CalcField like this I think (I'm assuming you're using NAV since that is required for CalcFields):
  
  =NL("CountUnique";"Transportzeile";"=NF(,""Customer"")&NF(,""Date"")";"shipping company";$E6;"Date";"01052012..31052012")
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 16, 2012 12:23

  Hello everyone,
  
  I have a table called "Transportzeile" which shows the sendings:
  
  Nr._____Date _________ Customer______Weight
  1_______03.05.2012____11111_________100 kg
  2_______03.05.2012____11111_________80 kg
  3_______03.05.2012____22222_________100 kg
  4_______03.05.2012____33333_________10 kg
  5_______03.05.2012____44444_________15 kg
  6_______04.05.2012____11111_________20 kg
  7_______04.05.2012____22222_________50 kg
  8_______04.05.2012____44444_________30kg
  9_______04.05.2012____44444_________20kg
  10______04.05.2012____55555_________55kg
  
  I want to count the sendings per month per customer (if there are 2 or more lines per day and customer it's 1 sending).
  
  The problem is:
  If I use the NL("Countuniqe") with datefilter like 03.05.2012 the results are right.
  E.g. Customer 11111
  03.05.2012=1
  
  But with datefilter 03.05.2012..04.05.2012 the results are wrong.
  E.g., Customer 11111
  03.05.2012 = 1
  04.05.2012 = 1
  result must be 2, but is 1.
  
  Is that understandable?
  
  Any ideas how to solve this?
  Thanks in advance!

• 0
  
  Jet Reports Historic Posts July 17, 2012 07:00

  Hi Hughes,
  
  good idea, but unfortunately this does not work for me, because I think I asked the wrong question… :oops:
  
  I need the number of sendungs for each shipping company.
  
  Nr._____Date _________ Customer______Weight____Shipping company
  1_______03.05.2012____11111_________100 kg_____A
  2_______03.05.2012____11111_________80 kg______A
  3_______03.05.2012____22222_________100 kg_____A
  4_______03.05.2012____33333_________10 kg______A
  5_______03.05.2012____44444_________15 kg______A
  6_______04.05.2012____11111_________20 kg______A
  7_______04.05.2012____22222_________50 kg______A
  8_______04.05.2012____44444_________30kg______A
  9_______04.05.2012____44444_________20kg______A
  10______04.05.2012____55555_________55kg______A
  
  So, my first formula is:
  =NL("rows";"shipping company";"Code";"creditor";"<>''";"+Code";"*")
  Then I need the sum of the shipped weight, which is no problem:
  =NL("Sum";"Transportzeile";"Weight";"shipping company";$E6;"Date";"01052012..31052012")
  Now, I need the number of sendings. And that is the problem!
  I've tried this one:
  =NL("CountUnique";"Transportzeile";"Customer";"shipping company";$E6;"Date";"01052012..31052012")result is 5. But that is not what I want to have as the result.
  the result should be 8 (4 sendings on 03.05.2012 and 4 sendings on 04.05.2012).
  
  Hope you can help me with that?!
  
  Regards,
  contrff

• 0
  
  Jet Reports Historic Posts July 18, 2012 14:48

  Yes, that works fantastically!!! :D
  
  Thank you so much!!