Solved: Re: Count based on Date - Qlik Community

Report Inappropriate Content · ‎2013-07-31

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.

jerem1234 · ‎2013-08-01

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])

View solution in original post

Report Inappropriate Content · ‎2013-07-31

I tried something like this to start, but no luck

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

jerem1234 · ‎2013-07-31

Maybe something like this:

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

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

Report Inappropriate Content · ‎2013-07-31

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

jerem1234 · ‎2013-07-31

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

Report Inappropriate Content · ‎2013-07-31

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],

Report Inappropriate Content · ‎2013-07-31

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

Report Inappropriate Content · ‎2013-07-31

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?

jerem1234 · ‎2013-07-31

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.

Report Inappropriate Content · ‎2013-07-31

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