Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count based on Date

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.

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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

10 Replies
Not applicable
Author

I tried something like this to start, but no luck

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

jerem1234
Specialist II
Specialist II

Maybe something like this:

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

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

Not applicable
Author

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

jerem1234
Specialist II
Specialist II

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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
Specialist II
Specialist II

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.

Not applicable
Author

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