0

filter 7 days and 28 days data

LAU JUEN LIANG

December 21, 2018 04:09

None

Follow

Hi JetReport!

Here is my question. I got a "age" column for my raw data, which record the data is for 7days or 28days.

Item No.  Age   Quantity
A               7         100
A               7         200
A             28         200
A             28         100
A             28         150
B             7           100
B             7           50
B             7           75
C             28         100
C             28         100
C             28         50
D             7           20
D             28         30

Anyway for me to write a jetreport filter, to filter out the data which contain "7 days and 28 days", in the example, will show the row data A and D only.Because A and D containt both 7 days and 28 days result.

If there's no 7 days results,  or if there's no 28 Days data ignore the data. 

Anyway to write the filter????

       

• Facebook
• Twitter
• LinkedIn

12 comments

• 0
  
  Cat December 28, 2018 09:23

  Hi,

  I am not really sure what you will filter.

  Could you mark the rows you would like to see?

• 0
  
  LAU JUEN LIANG January 03, 2019 08:08

  Hi Catharina,

  The row i would like to see will be
  
  Item No.  Age   Quantity

  A               7         100
  A               7         200
  A             28         200
  A             28         100
  A             28         150
  D             7           20
  D             28         30

  As B don't have 28 days quantity, and C don't have 7 Days Quantity.....

   

   

• 0
  
  Alexander Kannan January 04, 2019 08:47

  do you what this filters to be fixed or as a input

• 0
  
  LAU JUEN LIANG January 04, 2019 10:07

  hi alexander,
  i want to know how to write the filter in jetreport to give me that output...

• 0
  
  Cat January 07, 2019 09:15

  Hi,

  I would do it like this:

  NL Filter for age 7 and age 28, then the intersect of both filters. I hope, it is visible in the screenshot.

  F6: =NL("Filter";"Table";"Item No.";"DataSource=";$E$3;"Company=";$E$4;"Age";E6) => filter all items with 7 days

  F7: =NL("Filter";"Table";"Item No.";"DataSource=";$E$3;"Company=";$E$4;"Age";E7) => filter all items with 28 days

  D10: =NL("Rows=2";NP("Intersect";$F$6;$F$7)) => rows for the items with 7 and 28 days (hide it with condition if you do not want a header line => B10)

  E11: =NL("Rows";"TableName";;"DataSource=";$E$3;"Company=";$E$4;"Item No.";$D11) => retrieve Detail rows for the filtered items

   

  

   

• 0
  
  LAU JUEN LIANG January 09, 2019 07:31

  Hi Cat,
  
  Thanks for the guide, does Jet Report Version 9.1.9338.0 able to use NP("Intersect")??

   

• 0
  
  Harry Lewis January 09, 2019 18:36

  Hello Lau Juen Liang.

  While it has been 9 years since I used that version, I do seem to remember that it did include the NP("Intersect") function.

  The functionality of that function was changed slightly in Jet 2015:

  • Changed NP(Difference), NP(Intersect) and NL(AllUnique) to return "||" strings so that they no longer have to be embedded inside another Jet function

  and there have been a few other modifications to the NP(Interest) function, over the years.

  But, since Catharina's example is embedded in another Jet function, it shouldn't make a difference.

  Edited by Harry Lewis January 09, 2019 18:36
• 0
  
  LAU JUEN LIANG January 09, 2019 22:07

  hi harry,
  hmm... thats mean intersect is available in version 9...
  but weird i couldnt get any data i want.... 😨

• 0
  
  Harry Lewis January 09, 2019 23:03

  Hello -

  You can use the NP(Join) function to check the contents of your Filters and Intersects.

  Here is a simple example that I put together with Jet 19.4:

  

   

  When I run my report:

  

  If you use this technique and one or more cells display results you do not expect, you can look at why.

  Edited by Harry Lewis January 09, 2019 23:11
• 0
  
  LAU JUEN LIANG January 10, 2019 00:15

  Hi Harry, 
  
  Based on Cat's example here is the filter i created:
  
  =NL("Filter","Sales QC Line","Delivery Docket No.","Company=",$D$3,"+Item No.",$D$7,"+Line Status","Completed","Date Tested",$D$4,"Type","7 Day","Project Code",$D$6,"Location Code",$D$8,"Certificate Cube",$D$12)

  =NL("Filter","Sales QC Line","Delivery Docket No.","Company=",$D$3,"+Item No.",$D$7,"+Line Status","Completed","Date Tested",$D$4,"Type","28 Day","Project Code",$D$6,"Location Code",$D$8,"Certificate Cube",$D$12)

  and to show the intersect, here is the jfx i created.

  =NL("Rows=2",NP("Intersect",$F$9:$F$10))

   

  How to use NP(Join) to check my filtered data? 

  Edited by LAU JUEN LIANG January 10, 2019 00:34
• 0
  
  Harry Lewis January 10, 2019 01:01

  As I mentioned in my previous post, in versions prior to Jet 2015, the entire NL(Filter) function must reside inside of the NP(Intersect) function. Version 9 did not support having the NL(Filters) in a cell by themselves and then putting references to those cells in the NP(Intersect) function.

  Unfortunately, with the length of your Filter functions, you will probably not be able to include both of them within the Intersect function (I'm guessing that will exceed Excel's function-length limits).

• 0
  
  LAU JUEN LIANG January 10, 2019 01:05

  Hmm.. sounds like the idea is not work with my version :-(

  any other way to do the filtering to get the data i want?