Count Distinct numbers using FirstSortedValue

[rustyfishbones]

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

[rustyfishbones]

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

[sunny_talwar]

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

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

[rustyfishbones]

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

[sunny_talwar]

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

[rustyfishbones]

=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.

[sunny_talwar]

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

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

[sunny_talwar]

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]

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]

So I still don't have the correct expression