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.
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))
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))
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?
Are you asking me if the below expression look right? If they give you what you want, then probably yes.
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!
Works for me
dimention - sales person , date
expression- =aggr(max(Time),[sales person],date)