Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to write a set expression where I have Date and time filed
StartTime EndTime Status
11/28/2016 11/29//2016 C
11/27/2016 11/28/2016 C
11/27/2016 11/28/2016 C
11/28/2016 11/29/2016 P
I want to write an expression to count only Status C from today. Every morning I'll check for C for todays date and if it is yesterdays date it should show count of C as 0.
I've tried
=count({<STATUS={'C'},LastDay>} LOADSTATUS)
Last Day is my yesterday's date.
Or I should be using todays date also as filter? With above table I should get count of C for today as only 1 and not 3.
Thanks.
How about this:
Count({<Status = {'C'}, EndTime = {"=Floor(EndTime) = Today()"}>}Status)
I am assuming that you are comparing Today and Yesterday with EndTime
COUNT({<Status = {'C'}, EndTime = {'$(=Date(Today(),'MM/DD/YYYY'))'}>}Status)
StartTime EndTime Status
11/27/2016 11/28/2016 C
On which day should this record be counted?
For todays date
StartTime EndTime Status
11/28/2016 13:09:16 PM 11/28/2016 13:45:16 PM C
Actually Start & end time will be same but there will be time difference.
If it is for yesterdays date then Status count should be 0 and if it is 11/29/2016 then count should be 1.
I tried but exp gives error for MM/DD/YYYY field.
I think you see an error because of single quotes within single quotes, try this may be
Count({<Status = {'C'}, EndTime = {"$(=Date(Today(),'MM/DD/YYYY'))"}>}Status)
My start and end time also has time stamp
StartTime EndTime Status
11/28/2016 13:09:16 PM 11/28/2016 13:45:16 PM C
after using your expression and for testing purpose I changed the date to 11/29/2016 and the count still shows 0 but there is 1 record for today's date.
I'm facing this issue cus Date and Time are together. Or else the expression works fine.
How about this:
Count({<Status = {'C'}, EndTime = {"=Floor(EndTime) = Today()"}>}Status)
It worked, thanks!!!