Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
I tried something like this to start, but no luck
=if(date([Date Termed],'MM/DD/YYYY') < today(), Count([OwnerID]),0)
Maybe something like this:
=Count({<[Date Termed]={"<$(=today()), >$(=today()-7)"}>}[OwnerID])
Just make sure [Date Termed] is already in date format
I would make sure [Date Termed] is in date format in the script correct?
yep thats correct, you can use the date() function to do that
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],
added single quotes to the format 'mm/dd/yyyy' and everything seems ok. that correct?
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?
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.
The dates are coming into the system in the following format yyyy-mm-dd, for example 213-007-23