Count #records between two periods of the day (time)

[#1]

Hi,

Does anyone know what is wrong with the following expression?

=Count({$<[Incident creation time]={"<=$(=time(8/24))"}>} distinct [Incident ID])

The goal would be to find the number of records that have been created before 8h00 (A.M). The chart return records, but definately not the correct ones...

The ultimate goal would be to figure out how many records are present for which the creation time was either

- between 00h00 AM and 08h00 AM or

- between 06h00 PM and 24h00 PM

Ps: I tried the set analysis builder but that didn't work out either (no records found).

Any help would be appreciated.

Kind regards,

Maarten

[tresesco]

Load the time field using time() rather than date(), like:

Time(icdt_CreationTime,'hh:mm') as [Incident Creation Time]

[#3]

Dear Gysbert,

I tried your proposal as well but that seems to result in a count always equal to 1, regardless the linked record.

Thanks either way for your input!

Kind regards,

Maarten

[#4]

Tried that as well...

Ps: new cloud uploaded (this time with small set of data)

[tresesco]

Sorry, it should have been like:

Time(Frac(icdt_CreationTime),'hh:mm') as [Incident Creation Time]

[Gysbert_Wassenaar]

Your time field doesn't times, but timestamps (a.k.a. datetimes). So you need to use the frac function to get the time part of the timestamp:

ceil(frac([Incident Creation Time])*3)

[#7]

Thanks, that did the job!