Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

15 Replies
Highlighted
MVP
MVP

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

Highlighted
MVP
MVP

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

Highlighted
Not applicable

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!

Highlighted
Not applicable

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

Highlighted
Highlighted
Not applicable

Added a sample to the main posting.

Highlighted
MVP
MVP

does it works with an if?

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

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
Not applicable

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