topic Re: Count based on Date in QlikView

Count based on Date

Wed, 31 Jul 2013 13:17:32 GMT

I am trying to wirte an expression that does a count on the field [OwnerID] where the field [Date Termed] falls between the current_date and current_date -7 days. Ideally I would like if it just did a count on [ownerID] for the prior week.

Re: Count based on Date

Wed, 31 Jul 2013 14:01:05 GMT

I tried something like this to start, but no luck

=if(date([Date Termed],'MM/DD/YYYY') < today(), Count([OwnerID]),0)

Re: Count based on Date

jerem1234 — Wed, 31 Jul 2013 15:45:54 GMT

Maybe something like this:

=Count({<[Date Termed]={"<$(=today()), >$(=today()-7)"}>}[OwnerID])

Just make sure [Date Termed] is already in date format

Re: Count based on Date

Wed, 31 Jul 2013 15:49:37 GMT

I would make sure [Date Termed] is in date format in the script correct?

Re: Count based on Date

jerem1234 — Wed, 31 Jul 2013 15:51:23 GMT

yep thats correct, you can use the date() function to do that

Re: Count based on Date

Wed, 31 Jul 2013 16:08:22 GMT

I tried using the following code, but the script does not
compile it. It’s telling me it cannot find the field <MM>,
thoughts? It looks like I have syntax correct.

date([Date Hired], MM/DD/YYYY) as [Date Hired],

Re: Count based on Date

Wed, 31 Jul 2013 16:13:51 GMT

added single quotes to the format 'mm/dd/yyyy' and everything seems ok. that correct?

Re: Count based on Date

Wed, 31 Jul 2013 16:22:00 GMT

i tried that code, but no luck. I am returning a value of 0. I also tried updating the days to look back to -100 and still nothing. I am certain I had users term within the past 100 days. Would it be any easier if i just needed the count for the previous week. Maybe use the InWeek() function?

Re: Count based on Date

jerem1234 — Wed, 31 Jul 2013 17:26:46 GMT

If your data was in like a text format or a number format like 20130731 you'll have to use something like date#(FIELD, 'YYYYMMDD'). and then you can use date(date#(FIELD, 'YYYYMMDD'), 'MM/DD/YYYY') to get it in the format you want.

to get previous week, you could do something like:

=if(weekstart([Date Termed]) = weekstart(today()-7), Count([OwnerID]), Null())

or like

=if(weekstart(date#([Date Termed], 'FORMAT')) = weekstart(today()-7), Count([OwnerID]), Null())

where FORMAT is whatever format your data originally is when you are loading it in.

Re: Count based on Date

Wed, 31 Jul 2013 19:48:05 GMT

The dates are coming into the system in the following format yyyy-mm-dd, for example 213-007-23

Re: Count based on Date

jerem1234 — Thu, 01 Aug 2013 14:28:33 GMT

maybe try this in loadscript:

Date(Date#(DATEFIELD, 'YYYY-MM-DD'), 'MM/DD/YYYY') as NEWDATENAME

and replace DATEFIELD and NEWDATENAME with your fields.

Maybe you want beginning of week for each date too, then do:

Date(Weekstart(Date#(DATEFIELD, 'YYYY-MM-DD')), 'MM/DD/YYYY') as NEWWEEKSTARTNAME

and replace DATEFIELD and NEWWEEKSTARTNAME with your fields.

Then in the expression, do:

=Count({<NEWWEEKSTARTNAME = {"<$(=weekstart(today())), >=$(=weekstart(today()-7))"}>}[OwnerID])