topic Re: Counting records before a date in QlikView

Counting records before a date

stvn_003 — Wed, 13 May 2015 15:47:26 GMT

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

ramoncova06 — Wed, 13 May 2015 18:28:59 GMT

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

stvn_003 — Wed, 13 May 2015 19:30:47 GMT

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

maxgro — Wed, 13 May 2015 19:41:48 GMT

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

stvn_003 — Wed, 13 May 2015 20:26:21 GMT

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

sunny_talwar — Wed, 13 May 2015 20:31:24 GMT

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

stvn_003 — Wed, 13 May 2015 20:46:14 GMT

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

sunny_talwar — Wed, 13 May 2015 20:50:21 GMT

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

ramoncova06 — Wed, 13 May 2015 20:50:32 GMT

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

stvn_003 — Wed, 13 May 2015 20:58:12 GMT

Now it shows zero =(

Re: Counting records before a date

maxgro — Wed, 13 May 2015 21:04:52 GMT

post your doc, if possible

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

stvn_003 — Wed, 13 May 2015 21:23:07 GMT

considering the table of the previous example

Re: Counting records before a date

ramoncova06 — Wed, 13 May 2015 21:38:57 GMT

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

Re: Counting records before a date

stvn_003 — Wed, 13 May 2015 23:18:09 GMT

Yes, i try, but nothing changes

Re: Counting records before a date

ramoncova06 — Thu, 14 May 2015 00:27:34 GMT

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

sunny_talwar — Thu, 14 May 2015 02:23:18 GMT

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

stvn_003 — Thu, 14 May 2015 17:24:51 GMT

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

maxgro — Thu, 14 May 2015 18:10:52 GMT

maybe this

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

Re: Counting records before a date

stvn_003 — Thu, 14 May 2015 19:38:28 GMT

topic Re: Counting records before a date in QlikView

Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Re: Counting records before a date

Thank you very much Massimo, was just what I needed