topic Re: Count #records between two periods of the day (time) in QlikView

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

Fri, 30 May 2014 08:58:43 GMT

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

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

tresB — Fri, 30 May 2014 09:05:45 GMT

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

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

maxgro — Fri, 30 May 2014 09:11:19 GMT

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

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

Fri, 30 May 2014 10:02:51 GMT

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!

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

Fri, 30 May 2014 10:03:35 GMT

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

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

tresB — Fri, 30 May 2014 10:12:36 GMT

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

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

Fri, 30 May 2014 10:14:22 GMT

Added a sample to the main posting.

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

maxgro — Fri, 30 May 2014 10:14:59 GMT

does it works with an if?

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

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

Gysbert_Wassenaar — Fri, 30 May 2014 10:16:23 GMT

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.

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

Fri, 30 May 2014 10:23:40 GMT

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

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

tresB — Fri, 30 May 2014 10:40:10 GMT

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

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

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

Fri, 30 May 2014 10:40:48 GMT

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

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

Fri, 30 May 2014 10:51:10 GMT

Tried that as well...

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

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

tresB — Fri, 30 May 2014 10:55:52 GMT

Sorry, it should have been like:

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

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

Gysbert_Wassenaar — Fri, 30 May 2014 11:16:33 GMT

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)

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

Fri, 30 May 2014 11:51:25 GMT

Thanks, that did the job!