1 2 Previous Next 17 Replies Latest reply on Feb 29, 2016 1:52 PM by JuliaOlsen

counting unique customer ids in a found set

I have identified a flag to determine whether a sale occurred on my database. however sometimes the same customer can have a sale on the same day or with in a 30 day period etc.   I want to flag it as a sale because I want to add up my sales volume total but I also need to count the number of unique customers that has a sale during that period.

right now ... I have a summary field that sums the told number of sales for a specified period.  but what I really want is the number of distinct customers that had a sale for a specified period ... say the last 30 days.

Can you point me in the proper directions... thanks,

• 1. Re: counting unique customer ids in a found set

Hi.  Someone else might have a better way to do it, but I have done it like this:

Grab the UniqueValues custom function from the Brian Dunning site: FileMaker Custom Function:UniqueValues( values )

Then you can use this calculation to get the value you want:

UniqueValues ( List ( Orders::CustomerID_fk ) )

• 2. Re: counting unique customer ids in a found set

Here's simple script that will give you the count of Unique ids.

FMA 13

• 3. Re: counting unique customer ids in a found set

You can use this formula :

Let

(

[

no_of_days = 30 ;

startdate = Get ( CurrentDate ) - no_of_days ;

query = "SELECT DISTINCT Cust_ID FROM Sales WHERE SalesDate>'" & startdate & "'" ;

cust_id_list = ExecuteSQL ( query ; Char (9) ; Char (13) ) ;

no_values = ValueCount (cust_id_list)

] ;

no_values

)

Cust_ID = the field you want to count the unique values in the found selection

Salesdate = the field that contains the sales date

Sales = the name of the table

It works without any summary field or script...

• 4. Re: counting unique customer ids in a found set

Thanks for all the help guys. I will give it a shot.

• 5. Re: counting unique customer ids in a found set

LucThomaere

Can I put this in an ExecuteSQL Statement?

• 6. Re: counting unique customer ids in a found set

LucThomaere I want to select the disctint customer IDs from the group where myflag is = 1

SELECT DISTINCT __kf_Customer_Id FROM Finance WHERE _b_has_unit_sold_trailing_month ="1"

_b_has_unit_sold_trailing_month  flag is set when the sale falls in the last month.

• 7. Re: counting unique customer ids in a found set

Can I use Distinct Count instead?

• 8. Re: counting unique customer ids in a found set

You can use whatever name you want that FileMaker allows.

• 9. Re: counting unique customer ids in a found set

I just use the ExecuteSQL function, part of a normal calculation field

• 10. Re: counting unique customer ids in a found set

If the field _b_has_unit_sold_trailing_month is a Text Field, you should use ='1'

if it is a number field you should use =1

• 11. Re: counting unique customer ids in a found set

You can also use "SELECT DISTINCT COUNT ( Cust_ID) FROM..." and

then you don't have to use the ValueCount function

• 12. Re: counting unique customer ids in a found set

Thanks LucThomaere

Okay here's what I have.. but I only get a ? as a result..

ExecuteSQL("SELECT COUNT (DISTINCT __kf_Customer_Id) FROM Finance WHERE _b_has_unit_sold_trailing_month=1"; ""; "")

_b_has_unit_sold_trailing_month is a calculated field that returns a number..

I'm not sure where my syntax error is ... since I am getting a question mark as my result.

• 13. Re: counting unique customer ids in a found set

ALso curious since I will use this on a subsummary part whether I can group by the subsummary field in the ExecuteSQL statement to get the unique count by sales person?

• 14. Re: counting unique customer ids in a found set

I don't know where is the error, but COUNT(DISTINCT is very slow on FM, may be unusable.

1 2 Previous Next