Set Analysis

[MK9885]

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.

stalwar1‌

Thanks.

[MK_QSL]

I am assuming that you are comparing Today and Yesterday with EndTime

COUNT({<Status = {'C'}, EndTime = {'$(=Date(Today(),'MM/DD/YYYY'))'}>}Status)

[Gysbert_Wassenaar]

StartTime               EndTime          Status

11/27/2016          11/28/2016           C

On which day should this record be counted?

• 27th?
• 28th?
• both?

[MK9885]

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.

[MK9885]

I tried but exp gives error for MM/DD/YYYY field.

[sunny_talwar]

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)

[MK9885]

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.

[MK9885]

I'm facing this issue cus Date and Time are together. Or else the expression works fine.

[sunny_talwar · Accepted solution]

How about this:

Count({<Status = {'C'}, EndTime = {"=Floor(EndTime) = Today()"}>}Status)

[MK9885]

It worked, thanks!!!