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.
This is a long thread, would you be able to summarize it a little to know where you stand to help you better here
Tried, still not working
It will be helpful if you can send sample app with expected output
I opened up one of the files attached above and I see the Breen for Oct 2018 have only On-Time for CaseOnTime. I don't see any data for 8-15 Mins late? Am I looking at the wrong file?
A sample image above...
To explain in detail
I need to count only First Case which is in less than or equal to 7 Mins, With and exception, case should not be no later than 08:31 AM.
I have made a calculated dimension for case <=7 and no later than 08:31 as follows
if(Diff<=7,'On-Time') as CaseOnTime
Diff is already pre calculated in excel file.. but if you do Actual Start - Schedule Start, you should get same Diff.
if([Scheduled Start]<'8:31:00 AM',[Case ID]) as Lessthan8
With Less than 8, I get all the cases which are <=7 mins only, excluding which are greater than anything 08:45 AM or even 08:32 AM.
Now the 2nd part, to count on First Case on given day
If you see above image,
01/14/2019 has 2 cases
1st Schedule Start on 07:30 AM
2nd Schedule Start on 08:15 AM
I'd like to count only 07:30 AM Case ID=1120. Not 1122.
Same for all the Surgeons.
See attached data...
Breen Thomas has 5 OnTime Case & 5 First Cases. I'm getting 8 First Cases
Dimensions are
Year, Month, 2 other dimensions from different table
OnTime expression I'm using is count ( DISTINCT [On Time])
FirstCase I used count ({<DOS >} DISTINCT Lessthan8)
Where DOS= Date
I attached sample data.
Let me know if that doesn't help, will attach app.. but it has all my gibberish script.
The file/data might have changed since then or expectations from users..
One I just attached is accurate data
Would you be able to attached the app where this data is loaded... the reason I ask this is because the field names seems to be different and it would rather take us some time to load the data. You knowing the data might be able to do it much quickly then us.
yes, give me a min..
Thanks.
Try this
Thanks.