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]

I guess it would be something related to time format mismatch. Could you post your sample qvw with sample data?

[maxgro]

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

[#4]

Problem is the company policy doesn't allow me to post the cloud, but I'll try to isolate the problem to a seperate cloud. Keep you posted. Thanks!

[#5]

I tried this as well before posting this thread, but didn't work either (even when casting the time to a numeric value).

[tresesco]

See this: Preparing examples for Upload - Reduction and D... | Qlik Community

[#7]

Added a sample to the main posting.

[maxgro]

does it works with an if?

=Count(if ([Incident creation time]<8/24, distinct [Incident ID]))

[Gysbert_Wassenaar · Accepted solution]

Alternatively you can create a Shift field in the load script:

Load *, ceil( [Incident creation time]*3) as Shift

from .... ;

Your set analysis expression would then become =Count({$<[Shift]={1,3}>} distinct [Incident ID]) to find the incidents created between 00h00 AM and 08h00 AM or between 06h00 PM and 24h00 PM

Perhaps you'll need to use the time# function if your time field contains string values instead of numeric day fractions.

[#10]

Unfortunately not. No resulting records at all (even when casting everything to "num")