Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

1 Solution

Accepted Solutions

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

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.


talk is cheap, supply exceeds demand
15 Replies
MVP
MVP

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

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

MVP
MVP

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

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

Not applicable

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

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!

Not applicable

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

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

MVP
MVP

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

Not applicable

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

Added a sample to the main posting.

MVP
MVP

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

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)

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.


talk is cheap, supply exceeds demand
Not applicable

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

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

Community Browser