18 Replies Latest reply: May 14, 2015 3:38 PM by Steven Saenz

# Counting records before a date

Hello, I wonder what is the correct way to write this expression:

Count( {\$<price_ok = {N} , date <= {Today()-7} >} pk_oc )

• ###### Re: Counting records before a date

this should do it

Count( {\$<price_ok = {'N'} , date = {"<=\$(=Today()-7)"} >} pk_oc)

here more on set analysis A Primer on Set Analysis

• ###### Re: Counting records before a date

Maybe I'm doing something wrong because when I write this expression shows the number of matches

Count({\$<ocd_precio_ok = {N} >} ocd_cmtrlsap)

but when I write this just another expression shows zero

Count( {\$<ocd_precio_ok = {'N'} , occ_fentr = {"<=\$(=Today()-7)"} >} ocd_cmtrlsap)

• ###### Re: Counting records before a date

maybe you need to add a date or a date with the same format as occ_fentr

Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"<=\$(=date(Today()-7))"} >} ocd_cmtrlsap)

• ###### Re: Counting records before a date

Thanks Massimo

But, now two expressions show me the same result

Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"<=\$(=date(Today()-7,'DD/MM/YYYY))"} >} ocd_cmtrlsap)

• ###### Re: Counting records before a date

Do you have data in the last 7 days for ocd_cmtrlsap? If not then both the expressions are correctly showing the same count because there is no data to be counted between 05/07/2015 and 05/13/2015. If you do have data then would you be able to share a sample application to check what is going wrong?

Best,

Sunny

• ###### Re: Counting records before a date

what happens when you only use Count({\$<ocd_precio_ok = {'N'} >} ocd_cmtrlsap)

I am assuming that in your original expression you are actually getting a value because of the fact that you are not using the single quotes to define your constant to filter for ocd_precio_ok

• ###### Re: Counting records before a date

I have this information: ( ocd_cmtrlsap = Nro OC | occ_fentr = Fecha Entrega )

I want to count how many records there:

Count({\$<ocd_precio_ok = {N} >} ocd_cmtrlsap)

and how many records were 7 days ago:

Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"<=\$(=date(Today()-7,'DD/MM/YYYY))"} >} ocd_cmtrlsap)

But, now two expressions show me the same result, that is to say 6 rows

• ###### Re: Counting records before a date

You are missing a single quote after the date format here:

=Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"<=\$(=Date(Today()-7,'DD/MM/YYYY'))"} >} ocd_cmtrlsap)

May be that will resolve the issue

• ###### Re: Counting records before a date

Now it shows zero =(

• ###### Re: Counting records before a date

or remove the label of the expression with set analysis and you could see the expression as that in the image to check it

the format of the field and the format of the date must be the same

• ###### Re: Counting records before a date

considering the table of the previous example

• ###### Re: Counting records before a date

did you see my comment regarding the single quotes for ocd_precio_ok ?

• ###### Re: Counting records before a date

Yes, i try, but nothing changes

• ###### Re: Counting records before a date

so Count( {\$<ocd_precio_ok = {'N'} >} ocd_cmtrlsap) and Count( {\$<ocd_precio_ok = {N} >} ocd_cmtrlsap) gives you the same number ?

• ###### Re: Counting records before a date

Try this may be this might give you the result you are looking for.

=Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"\$(='<=' & Date(Today()-7,'DD/MM/YYYY'))"} >} ocd_cmtrlsap)

• ###### Re: Counting records before a date

Hi, here I upload a sample file so that they can understand better. Thanks for the support

Column should have all the records (6), but only column B that has a date (column C) above or equal to 08/05/2015 (4)

• ###### Re: Counting records before a date

maybe this

Count( {\$<ocd_precio_ok = {N}, occ_fentr = {"<=\$(=Date(Today()-6,'DD/MM/YYYY hh:m:ss tt'))"} >} ocd_cmtrlsap)