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.

[MK9885]

IF you can use CaseOnTime as your dimension for Bar graph & see if it makes any sense?

For 2018 Oct, Breen should have Count as 4 on time and then 8 to 15 mins as 1 Count.

Total 5 counts only.

[Arthur_Fong]

Looks fine:

[Kushal_Chawda]

If I create straight table with below dimension & expression it seems to work

Dimension:

DOS

Surgeon

Expression:

Case ID-

=aggr(FirstSortedValue([Case ID],[Sched Start]),DOS)

Sched Start -

=aggr(FirstSortedValue([Sched Start],[Sched Start]),DOS)

[MK9885]

YEs, but I just need single dimension or a drill down actually in a Bar graph

First dim as CaseOnTime

Second drill down to Surgeon

Firstsortedfavlue may not work on BAR graph

[Kushal_Chawda]

what will b expression of chart? count of Case id?

[MK9885]

I'm/was using Count(CaseOnTime).

But this gives me all the cases... And I need just the First case ID on any given day, if they have multiple cases.

And rest, count as normal.

And using CaseOnTime as Dim.

Drilling down to Surgeon

[Kushal_Chawda]

Still not clear to me what is the actual requirement.

Can you create the barchart with dimension CaseOnTime and below expression

=count(aggr(FirstSortedValue([Case ID],[Sched Start]),DOS,CaseOnTime))

above expression will give count of first case id. Using this expression can you check the count is correct? if not please tell clear requirement that in barchart what you want to display. I

[MK9885]

Correction ***

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 🙂

[MK9885]

Tried this expression and tested with more detail & it doesn't work.

So, back to square one 😞

Does anyone still have solution to this?

I tried Fristvalue, Firstsortedvalue, Min, aggr etc etc..

Nothing seem to work.

@Kushal_Chawda @Arthur_Fong @sunny_talwar 

[Kushal_Chawda]

may be try

Count( If(Aggr(NODISTINCT Min([Sched Start]), CaseOnTime,DOSMonth) = [Sched Start], CaseID))