How do I show only an earliest value in a selected day..
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
Load Case ,
FirstValue(Value) as Value
Resident Dim Group by Case;
This gives me the answer but in expression it's getting messed up.
I used your expression @ Kush. Though the counts were coming correct, it wasn't tagging correct CASE ID's
So Instead I used below expression
Count(If(Aggr(NODISTINCT Min([Sched Start]), CaseOnTime,DOSMonth) = [Sched Start], CaseOnTime))
Also changed my calculated dimension in back end script for one of the main dimensions-CaseOnTime.
Thanks a lot Kush and also thanks a lot to Arthur for helping me sort this out 🙂
Raw: load * inline [ 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 ](delimiter is ' '); NoConcatenate Data: load if(RowNo()=1,1,if(SalesPerson=peek(SalesPerson),peek(Flag)+1,1))as Flag, Case,Value,Time#(Time,'h:mm tt') as Time,Date#(Date,'MM/DD/YYYY')AS Date,SalesPerson resident Raw order by SalesPerson, Date, Time; drop table Raw; NoConcatenate Data2: load * resident Data where Flag=1; drop table Data; exit script;
MK, did you want further help on things, your last post may have confused folks, so I just wanted to suggest following up if you did want further ideas. The only other thing I can offer is the Design Blog area link, we have hundreds of how-to posts out there, do not know if there is anything specific on what you are trying to do here, but I will let you search yourself on this one given I am not quite sure which one(s) may be most applicable.
Please see attached file.
I'm looking at 10/1/2018 has 2 CASE ID for same day
One starts at 07:30
another at 08:15
So i;d like to only see 07:30 when I select year 2018, month Oct and Surgeon.
For security reason, I've scrambled Surgeon names.