Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
rustyfishbones
Honored Contributor II

Count Distinct numbers using FirstSortedValue

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


Al

12 Replies
rustyfishbones
Honored Contributor II

Re: Count Distinct numbers using FirstSortedValue

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

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

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

rustyfishbones
Honored Contributor II

Re: Count Distinct numbers using FirstSortedValue

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

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

rustyfishbones
Honored Contributor II

Re: Count Distinct numbers using FirstSortedValue

=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

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

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

rustyfishbones
Honored Contributor II

Re: Count Distinct numbers using FirstSortedValue

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

rustyfishbones
Honored Contributor II

Re: Count Distinct numbers using FirstSortedValue

So I still don't have the correct expression