Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Above are 2 examples...
In Yellow, they should not show, cus on same date, we had a case earlier than 08:30.
For Breen, which 5 First cases will be counted?
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.
I hope below helps?
Examples:
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:
The logic has to meet the following to be included:
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
Will check this now and will get back if I have more questions
Can you check if this works for you
Count({<CaseOnTime = {'On-Time'}, Lessthan8 *= {"*"}>} [Case ID])
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
Which Month and Year are you looking at for Brown, Michael?
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)