Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

Show only earliest value in a day

Hi,

How do I show only an earliest value in a selected day..

Ex: 

CaseValueTimeDateSalesPerson
1ABC6:00 AM1/1/2019Mary
2BCD6:30 AM1/1/2019Mary
3CDE8:50 AM2/2/2019Mary
4EFG10:00 AM9/15/2019John
5HIJ12:00 PM3/22/2019Doug
6KLM7:30 AM5/5/2019Andrew
7NOP7:15 AM5/5/2019Andrew

 

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.

Labels (1)
56 Replies
sunny_talwar

This is a long thread, would you be able to summarize it a little to know where you stand to help you better here

MK9885
Master II
Master II
Author

Tried, still not working

Kushal_Chawda

It will be helpful if you can send sample app with expected output

sunny_talwar

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?

image.png

MK9885
Master II
Master II
Author

2020-01-29_113257.png

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

 

MK9885
Master II
Master II
Author

I attached sample data.

Let me know if that doesn't help, will attach app.. but it has all my gibberish script.

MK9885
Master II
Master II
Author

The file/data might have changed since then or expectations from users..

One I just attached is accurate data

sunny_talwar

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.

MK9885
Master II
Master II
Author

yes, give me a min..

Thanks.

MK9885
Master II
Master II
Author

Try this

Thanks.