topic Re: Count Distinct numbers using FirstSortedValue in QlikView

Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 19:57:16 GMT

Hi All,

I am trying to do a count using the below expression

=AGGR(count ({<DataValue = {"True"}, ParameterChildName = {"SigPod_AirTest_1_Status.Test_Passed"}, EntryTimestamp = {'$(=MIN(EntryTimestamp))'}>} distinct [Serial Number]),EntryTimestamp)

The result returns 1

should I be using FirstSortedValue in the Set Expression

I am trying to count the number of Serial Numbers where the Data Value is True and the ParameterChildName = SigPod_AirTest_1_Status.Test_Passed and I want to only count the Serial Numbers for the first Entrytimestamp as there can be multiple.

I understand I can count the distinct Serial numbers using DataValue and ParameterChildname, but I need to inlude only the first instance of each EntryTimestamp.

I hope you can help

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:03:21 GMT

I have the following which returns the First Serial Number

=FirstSortedValue(Aggr( distinct [Serial Number], EntryTimestamp), Aggr(EntryTimestamp,EntryTimestamp))

but I need to count the Serial Numbers

Re: Count Distinct numbers using FirstSortedValue

sunny_talwar — Tue, 14 Jun 2016 20:08:52 GMT

Not 100% sure, but can you try the below expression:

Count(Aggr(FirstSortedValue(Distinct [Serial Number], EntryTimestamp), EntryTimestamp))

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:18:25 GMT

That Returns 250.

So it returns all of the EntryTimestamps not the first for each Serial Number.

There are 5 records that contain 2 Entrytimestamps, so the result I need is 245

Thanks for your help, I can use this and make changes to check the results.

Regards

Alan

Re: Count Distinct numbers using FirstSortedValue

sunny_talwar — Tue, 14 Jun 2016 20:18:50 GMT

On another thought if EntryTimestamp is truly a timestamp field, may be you only have one serial number available?

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:20:18 GMT

=Count(Aggr(FirstSortedValue(Distinct [Serial Number], EntryTimestamp), [Serial Number]))

This returned the correct result, but I need to double check I have the correct Serial Numbers

Al.

Re: Count Distinct numbers using FirstSortedValue

sunny_talwar — Tue, 14 Jun 2016 20:23:56 GMT

I might be totally misguiding you, but can you try this:

Count({<DataValue = {"True"}, ParameterChildName = {"SigPod_AirTest_1_Status.Test_Passed"}>} Distinct EntryTimestamp)

Re: Count Distinct numbers using FirstSortedValue

sunny_talwar — Tue, 14 Jun 2016 20:25:32 GMT

This would calculate all the timestamps one time and I guess this is what you are trying to do. You are wanting to calculate 1st serial number for each timestamp. Does it matter if serial number is 10 or 100? We are just doing a count here. If you want to know what the value of the serial number, then this become a little more complex

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:26:54 GMT

I need to get the Serial Numbers for each of the First EntryTimestamp.

So I don't need to use Distinct, as {<DataValue = {"True"}, ParameterChildName = {"SigPod_AirTest_1_Status.Test_Passed"}>} + the FirstSortedValue should provide what I need

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:27:25 GMT

So I still don't have the correct expression

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:29:49 GMT

I think this maybe it

=Count({<DataValue = {"True"},ParameterChildName = {"SigPod_AirTest_1_Status.Test_Passed"}>} Aggr(FirstSortedValue(Distinct [Serial Number], EntryTimestamp), [Serial Number]))

but again, I need to double check my results.

Thanks for your help

Re: Count Distinct numbers using FirstSortedValue

sunny_talwar — Tue, 14 Jun 2016 20:44:45 GMT

Sorry, I might be repeating myself, but do you want the value of serial number or the count?

Re: Count Distinct numbers using FirstSortedValue

rustyfishbones — Tue, 14 Jun 2016 20:46:52 GMT

The Count of the Serial Numbers.