Show only earliest value in a day

[MK9885]

Hi,

How do I show only an earliest value in a selected day..

Ex: 

Case	Value	Time	Date	SalesPerson
1	ABC	6:00 AM	1/1/2019	Mary
2	BCD	6:30 AM	1/1/2019	Mary
3	CDE	8:50 AM	2/2/2019	Mary
4	EFG	10:00 AM	9/15/2019	John
5	HIJ	12:00 PM	3/22/2019	Doug
6	KLM	7:30 AM	5/5/2019	Andrew
7	NOP	7:15 AM	5/5/2019	Andrew

I'd like to show/count Case 1 for Mary for date 01/01/2019 cus that's the earliest case (with time stamp) for her that day and not count case 2 or case 3.

Same for Sales Person Andrew

Would like to count only Case # 7 as that was done 07:15 AM

I tried Peek and also Firstvalue

NoConcatenate

Data:

Load Case ,

FirstValue(Value) as Value

Resident Dim Group by Case;

This gives me the answer but in expression it's getting messed up.

Thanks.

[MK9885]

Above are 2 examples...

In Yellow, they should not show, cus on same date, we had a case earlier than 08:30.

[sunny_talwar]

For Breen, which 5 First cases will be counted?

[MK9885]

Below Case ID's should be First Cases for the Day

1026,

1120,

993,

825,

1247

OnTime would be 5 Cases

1026

1120

993

825

1247

Though count is same for both On Time or First Case

But On Time is <=7 Time Diff

First Case is Only the First Case for that Day which is also OnTime (<=7)

On Time Start = “Operation Start Time” </= 7 minutes from “Scheduled Start Time” for only the 1st case of Surgeon on a given day.

Exception: If first case is later than 8:30 AM, exclude. In other words, a first case must be between 7:30 and 8:30 AM.

[MK9885]

I hope below helps?

Examples:

• First case scheduled at 0800 for Dr. Kim, Patient in the OR Room at 0807 = On Time
• First case scheduled at 0900 for Dr. Wong, Patient in the room at 0901 = Not a first case since it does not meet the definition of at/or prior to 0830.  This case is excluded from calcualtions.
• First case scheduled at 0700 for Dr. Busconi, Patient in the room at 0708 = First Case but Not On time

On Time Starts is <=7 mins with exception of case not beyond 08:31 AM – Yes, On time start is <=7 minutes and must be scheduled to start before 0831 AM

As for First Case, what if first case is after 09:30 AM or even 11 AM? Not a first case, exclude this  from calculation even if it is a first case for that provider.   Example Dr. Henczel first case scheduled at 1200 – Does not meet the definition of first case.

Is it still considered as First Case for the day, but not On Time?  After 0830, no longer considered a first case and should be excluded.

Then we have a special exception:

• Breen, first case scheduled at 0730 and is in the room at 0735. 
  • First case and on time
• Breen, same day, second case scheduled at 0815 and is in the room at 0830.
  • Not a first case and should be excluded from calculations.

The logic has to meet the following to be included:

1. Must be the first case of the day for that provider
   1. If Yes, Move to #2
   2. If No, Exclude from data
2. Must be scheduled prior to 0831 AM
   1. If Yes, Move to #3
   2. If No, Exclude from Data
3. Must be OR In Room  <= 7 minutes
   1. If Yes, On Time
   2. If No, NOT On Time

[MK9885]

Edit:

To consider it as First Case

Schedule start should be before 08:31 AM & should be the First Case.

On Time is 

Should be before 08:31, First Case and <=7

[sunny_talwar]

Will check this now and will get back if I have more questions

[sunny_talwar]

Can you check if this works for you

Count({<CaseOnTime = {'On-Time'}, Lessthan8 *= {"*"}>} [Case ID])

[MK9885]

Thanks Sunny but it gives 2 counts for Michael Brown.

Please see attached sample...

Sample Ontime 1 measure: =Count(Aggr(FirstSortedValue( Timestamp([Scheduled Start], ' hh:mm TT'), -[On Time]), DOS))

Sample Ontime 2 measure count ( DISTINCT [On Time]) 

First Case: =Count(Aggr(FirstSortedValue( Timestamp([Scheduled Start], ' hh:mm TT'), -Lessthan8), DOS,Surgeon))

Sample 3 is your measure: Count({<CaseOnTime = {'On-Time'}, Lessthan8 *= {"*"}>} [Case ID])

I'm not using CaseOnTime dimension, Instead using [On Time]

Count(Aggr(FirstSortedValue( Timestamp([Scheduled Start], ' hh:mm TT'), -[On Time]), DOS)) <<< This expression, I got it from one of you examples 

https://community.qlik.com/t5/New-to-QlikView/FirstSortedValue-Count/td-p/1187634

Seem to work but still have to fully test it out

[sunny_talwar]

Which Month and Year are you looking at for Brown, Michael?

[MK9885]

Jan 2019

First Case for the day should be before 08:31 Scheduled start and First Instance only.

OnTime case should also be First Case like above and should be <=7 Mins (Actual Start-Schedule Start)