
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess it would be something related to time format mismatch. Could you post your sample qvw with sample data?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=Count({$<[Incident creation time]={"<=$(=(8/24))"}>} distinct [Incident ID])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I tried this as well before posting this thread, but didn't work either (even when casting the time to a numeric value).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Added a sample to the main posting.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
does it works with an if?
=Count(if ([Incident creation time]<8/24, distinct [Incident ID]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately not. No resulting records at all (even when casting everything to "num")

- « Previous Replies
-
- 1
- 2
- Next Replies »