8 Replies Latest reply on Apr 24, 2014 3:16 PM by Vincent_L

# I wish there were two functions 1-Median Calculation 2-Count of Unique Records

Median calculation is a pain taking one now...Wish field Summmary have another selection of Median....

The other feature I would like to see is a counting function of duplicate/unique(not sure how to word it) records...

Example:

Number Text Date

1890 First, Second 5/1/14

2340 Third, Ten 8/1/14

1890 First, Second 5/15/14

1890 First, Second 5/15/14

5310 Seven, One 10/1/14

5310 Seven, One 12/1/14

Question: How many unique Numbers are there? The answer should be:3 (1890, 2340, and 5310)

• ###### 1. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

hi jayan,

for the second problem exists a simple solution. Assuming you have some way to determine the current 'selection', use the ExecuteSQL function with the DISTINCT operator:

ExecuteSQL ( "SELECT DISTINCTS Number FROM MyTable ") - you can count the rsult to get to the requested number of unique "numbers".

From the FMP13 documentation:

The DISTINCT operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example:

`    SELECT DISTINCT dept FROM emp `

Regards,

Volker

• ###### 2. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

or you could use SELECT COUNT ( DISTINCT dept ) FROM emp as your select statement.

• ###### 3. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

If there were going to be a Median function, I'd prefer that it be implemented as a more general Quantile function instead so that we could ask for 25th percentile or 90th percentile, etc. The Median may or may not be the 50th percentile, depending on your definition of Median.

• ###### 4. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

COUNT ( DISTINCT

works, but very slow.

This issue is not resolved on FM13v3 yet.

• ###### 5. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

There's also a few custom functions that can help. UniqueValues() is a good one. There's also GetNthRecordSet(). Check out briandunning.com for more info about them. With them, a simple calc:

=ValueCount ( UniqueValues ( GethNthRecordSet ( Number ) ) )

• ###### 6. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

Thank you Volker,

I tried your suggested calculation long time ago and I was getting the ? as the answer.

What I done (want) was created a calculation field with the execute SQL statement and placed the field in my form view of my database.

When I perform the search (Example: date range 1/1/14...3/31/14) the serach result should display my unique count in form view, and later I need to place this field in my report layout as well.

Someone suggested the following calculation, and this also giving me the ?

ExecuteSQL (

"SELECT " &

"COUNT (DISTINCT \"MR#\") " &

"FROM " &

"\"yourTable\" "

;"";""

)

Thank you

Jayan

• ###### 7. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

jayankurian wrote:

Someone suggested the following calculation, and this also giving me the ?

ExecuteSQL (

"SELECT " &

"COUNT (DISTINCT \"MR#\") " &

"FROM " &

"\"yourTable\" "

;"";""

)

This should work – provided you use an existing field and TO name – but why concatenate the strings instead of using a single one? SQL doesn't care about whitespace:

ExecuteSQL ( "

SELECT COUNT ( DISTINCT \"MR#\" )

FROM \"TableName\"

" ; "" ; ""

)

If, as was suggested, COUNT DISTINCT is slow, you could simply list the values and wrap the expression into ValueCount():

ValueCount (

ExecuteSQL ( "

SELECT DISTINCT \"MR#\"

FROM \"TableName\"

" ; "" ; ""

) )

or use a Custom Function, e.g.

CollectDistinct ( theList ) =

Case (

not IsEmpty ( theList ) ;

Let ( [

v = GetValue ( theList ; 1 ) ;

rest = Substitute ( theList & ¶ ; v & ¶ ; "" )

] ;

List ( v ; CollectDistinct ( Left ( rest ; Length ( rest ) - 1 ) ) )

)

)

and ValueCount() that.

• ###### 8. Re: I wish there were two functions 1-Median Calculation 2-Count of Unique Records

The Excellent free BaseElements Plug-In as fast BE_Values_Unique ( listOfValues ) function, that alone divided one of my script time by two !