topic Re: Show only earliest value in a day in QlikView

Show only earliest value in a day

MK9885 — Sat, 16 Nov 2024 19:29:34 GMT

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.

Re: Show only earliest value in a day

Arthur_Fong — Tue, 10 Dec 2019 01:07:58 GMT

Try this:

Script:

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;

Re: Show only earliest value in a day

MK9885 — Tue, 10 Dec 2019 16:39:15 GMT

Thanks, I'll test this script and let you know.

Re: Show only earliest value in a day

MK9885 — Wed, 11 Dec 2019 15:25:30 GMT

It did not work...

It created duplicate rows with blanks...

Thanks a lot anyway.

Re: Show only earliest value in a day

Brett_Bleess — Tue, 17 Dec 2019 18:45:42 GMT

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.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Show only earliest value in a day

MK9885 — Mon, 13 Jan 2020 16:25:22 GMT

Anyone who can help me with this?

@sunny_talwar or anyone?

Thanks.

Re: Show only earliest value in a day

Arthur_Fong — Mon, 13 Jan 2020 16:36:11 GMT

You have a sample app? The one you tested with duplicated blank records?

Re: Show only earliest value in a day

MK9885 — Mon, 13 Jan 2020 16:43:57 GMT

I'll mock up an app for you...

5 mins.

Thanks

Re: Show only earliest value in a day

Kushal_Chawda — Mon, 13 Jan 2020 17:01:24 GMT

Create a straight table

Dimension:

Case,

Date,

Time,

Value

Expression: (Sales Person)

=aggr(FirstSortedValue(SalesPerson,Time),SalesPerson)

Re: Show only earliest value in a day

MK9885 — Mon, 13 Jan 2020 17:11:46 GMT

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.

Re: Show only earliest value in a day

MK9885 — Mon, 13 Jan 2020 17:12:30 GMT

Tried, did not work.

Thanks

Re: Show only earliest value in a day

Arthur_Fong — Tue, 14 Jan 2020 02:13:31 GMT

Try this:

Re: Show only earliest value in a day

MK9885 — Tue, 14 Jan 2020 02:32:20 GMT

Thank you..

I'll test this tomorrow?

A quick check looks good... I'm having like a complete data model, need to verify across all dashboard.

So far it's good.. thanks a lot.

Re: Show only earliest value in a day

MK9885 — Tue, 14 Jan 2020 02:35:48 GMT

This expression you gave seem to work only in straight table.

Not working in Bar chart.. maybe cus using RANK?

Re: Show only earliest value in a day

Arthur_Fong — Tue, 14 Jan 2020 03:27:21 GMT

Can you attach the app?

Re: Show only earliest value in a day

MK9885 — Tue, 14 Jan 2020 16:20:06 GMT

I'm trying to count earliest case for that day for that particular person.

Rest should also show up... expression you gave only shows 1st case but doesn't show rest of the cases.

Counting all the cases but if there are multiple cases on same day, I would like to count only 1st case for the day, not multiple.

Re: Show only earliest value in a day

Arthur_Fong — Tue, 14 Jan 2020 16:25:15 GMT

Can you attach your expected result?

Re: Show only earliest value in a day

Arthur_Fong — Tue, 14 Jan 2020 16:43:37 GMT

Do you mean like this?

Can you also share the issue you are having with bar chart?

No issue with me creating this table in bar chart.

Re: Show only earliest value in a day

MK9885 — Tue, 14 Jan 2020 16:52:29 GMT

If I clear all selections & use Year filer 2018, Month Oct, I should get 5 counts.

If you look at data I gave you, that person has total count as 6. But since we are doing 1st case for the day, 10/01/2018 at 07:30 AM will be counted, not the second case on that day.

Actual app consists of a lot of data sources. thats why i cannot upload it here

I'm using a calculated dimension for this chart, which says if variance is <=7, term is at On-time, between 8-15 as late etc..

Re: Show only earliest value in a day

Arthur_Fong — Tue, 14 Jan 2020 17:02:44 GMT

You are counting case on time here,which gives you 3, rather than 6:

I would suggest you to count by Case ID.