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.

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(
  
  If(
    
    Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) = [Scheduled Start] and
    Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) < MakeTime(8, 31) and
    Len(Trim(CaseOnTime)) > 0
  
  , 1, 0)

, Year, Month, DOS, [Case ID], [Acct #], Surgeon, [Scheduled Start], [Actual Start], Diff))

[MK9885]

Yes, it works for First Case

And for OnTime which is same as First Case but <=7

I used below with slight modification

Sum(Aggr(

If(

Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) = [Scheduled Start] and
Min(TOTAL <Surgeon, Year, Month, DOS> [Scheduled Start]) < MakeTime(8, 31) and
Len(Trim([On Time])) > 0

, 1, 0)

, Year, Month, DOS, [Case ID], [Acct #], Surgeon, [Scheduled Start], [Actual Start], Diff))

[MK9885]

One you gave also works along with again, which you had previously told someone

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

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

What do you think about above 2?

[sunny_talwar]

Are you asking me if the below expression look right? If they give you what you want, then probably yes.

[MK9885]

Ok thanks...

I'll wait until users do their QC to mark it as correct so I don't lose your attention?

thanks a lot again for your help!

[sunny_talwar]

Works for me

[sayalip]

dimention - sales person , date

expression- =aggr(max(Time),[sales person],date)