Number of values

[xlello]

Hallo,

I tried to find another topic to answer my question... bad luck or bad finding skills :

I'm trying to count the number of different values in a text or data field.

eg:

Field -> date

1-1-2006 present in 3 records

2-1-2006 present in 6 records

3-1-2006 present in 9 records

my desired outcome should be 3 (3 different dates in 18 records)

"count" function returns 18 :

I need this to average a number of contacts (1 each record) by working days

as in previous example:

18 (contacts) / 3 (worked days) = 6 contact per day (average)

I'm just switched to FM 8.5 Advanced

I appreciate any help.

Thanks

:

[John Mark Osborne]

What you posted makes sense. But, how will be using this value once you get it? Is it for a report? How often do you need to get this value from the found set? Give us more details about how you want to use this value so we can provide an appropriate solution. Also, why are your date values in a text field or am I just reading your post incorrectly?

[DukeS]

If you need this number here is a solution. It is probably not the best one but it works...

You create summary field and set it to Fraction of Total for your date field. Then check subtotaled and select same date field in when sorted by column.

Create another global number field.

Create script with loop like this:

SortRecords[YourDateField; Ascending]

SetVariable [$count; 0]

GoToRecord[first]

Loop

SetVariable [$count; $count + YourSummaryField]

GoToRecord[next; exit after last]

End Loop

SetField [GlobalNumberField; $count]

When you run the script your number field should have count of summarized dates.

HTH

[Raybaudi]

Hi

what about a calc like this:

Evaluate ( ValueCount ( ValueListItems ( Get ( FileName ) ;"dates" ) ) ; date )

where "dates" is a list coming from values of field "date" ?

[T-Square]

Or a self-join on the date field and putting it all on a report with a sub-summary when sorted by date?

David

[xlello]

how will be using this value once you get it? Is it for a report? How often do you need to get this value from the found set? Give us more details about how you want to use this value so we can provide an appropriate solution.

I need this computation quite often... my goal is to obtain an average of records per worked day and use this number in analisys and reports.

I'm not able to find a function to count the number of values ina specified field (date or text) in a specified foundset

eg:

Field -> city

Record1 -> New York

Record2 -> Paris

Record3 -> Paris

Record4 -> Paris

I'd like a 2 (rome + paris) as outcome

what about a calc like this:

Evaluate ( ValueCount ( ValueListItems ( Get ( FileName ) ;"dates" ) ) ; date )

This works but it counts all dates in my DB not the number of dates in a founset

Thank you guys for your support!!

Edited October 24, 2006 by Guest

[Raybaudi]

This works but it counts all dates in my DB not the number of dates in a founset

You can get the values from the field:

selfjoin::date

(include only related values)

[comment]

Any solution based on a relationship/value list will ignore the found set. If you need this for a report, you can make a sub-summary part based on the break field and use a summary field to count the records. A calculation using GetSummary (summary field ; break field) will return the same result 'live' - provided records are sorted by break field.

[xlello]

If you need this number here is a solution. It is probably not the best one but it works...

You create summary field and set it to Fraction of Total for your date field. Then check subtotaled and select same date field in when sorted by column.

Create another global number field.

Create script with loop like this:

SortRecords[YourDateField ; Ascending]

SetVariable [$count; 0]

GoToRecord[first]

Loop

SetVariable [$count; $count + YourSummaryField]

GoToRecord[next; exit after last]

End Loop

SetField [GlobalNumberField; $count]

When you run the script your number field should have count of summarized dates.

Duke's script counts the number of days :

I need this value to make further computation in other reports and I can't belive there is no function (similar to valuecount) to count the number of different values in a field, in a foundset :qwery:

[comment]

I can't belive there is no function (similar to valuecount) to count the number of different values in a field, in a foundset

Mhmmm... perhaps we missed it among the multitude of functions provided by Filemaker (238 by my count).

If you absolutely need it as a calculation that returns the same result for all records in the found set, there's a way to do this with a custom function or two - but it's quite complex. Perhaps you should explain what "further computation in other reports" is required - there might be a simpler way to attack the real problem.

[Raybaudi]

Any solution based on a relationship/value list will ignore the found set.

that's true

It seems that I need more slipping-time

[xlello]

If you absolutely need it as a calculation that returns the same result for all records in the found set, there's a way to do this with a custom function or two - but it's quite complex. Perhaps you should explain what "further computation in other reports" is required - there might be a simpler way to attack the real problem.

I need an average of contacts per day in a foundset.

eg. How many contacts per day (average) I had in may 2006 (foundset over all 2006)??

I'm able to compute in a report how many contact per day I had but I'm not able to average them :

if:

1-1-2006 Paola

1-1-2006 Jhon

1-1-2006 Sally

2-1-2006 Rose

2-1-2006 Peter

I'm able to recieve:

1-1-2006 3 contacts

2-1-2006 2 contacts

I wish to recive

2,5 contacts per day in 2 days

: :

Edited October 25, 2006 by Guest

[comment]

See if this helps.

CountGroups.fp7.zip

[xlello]

See if this helps.

It works :

I just need little time to understand wath you did : :

Thanks!!!!

[comment]

I just need little time to understand wath you did

As soon as you do, be sure to tell me too. Because it's not my technique (it's been around for a while, and I don't know who deserves the credit for it), and I can only understand it on a good day.

Note that it's rather expensive in terms of number of fields required, and none too fast. If you can settle for a scripted result, I would go for that instead. Using Edoshin's FastSummaries, a script can do this very quickly.

[xlello]

Ok folks,

I used DukeS's script

Create script with loop like this:

SortRecords[YourDateField ; Ascending]

SetVariable [$count; 0]

GoToRecord[first]

Loop

SetVariable [$count; $count + YourSummaryField]

GoToRecord[next; exit after last]

End Loop

SetField [GlobalNumberField; $count]

and added Comment's field "average per date"

great result..... an average of records per date

now.... little more complicate...

what if I wish to get:

How many contact per day (average) I have per each city??

eg.

New York

3 contacts per day

Los Angeles

2 contacts per day

Boston

4 contacts per day

I guess I need to sub-summarize but I really don't know how...

please, help a poor newbie :

Edited October 26, 2006 by Guest

[comment]

That changes the picture considerably, I think. How many cities are there, and are they more or less constant? Also, what is your find criteria?

[xlello]

That changes the picture considerably, I think. How many cities are there, and are they more or less constant? Also, what is your find criteria?

That's the big picture...

Fields in a recod:

ID

Date

Contact name

City

Operator

I already use a report to summarize number of contacts per city and per operator

eg.

Paris 5 contacts

operatorA 2 contacts

operatorB 3 contact

New York 6 contacts

operatorC 4 contacts

operatorD 2 contacts

Now, I wish to have a report like this:

operatorA 1 contact per day (average of contacts per worked days)

operatorB 1,5 contact per day (average...)

....and so on

or ...even more accurate and complicate...

New York

operatorA 1 contact per day (average...)

operatorB 1,5 contact per day (average...)

Paris

operatorC 2 contacts per day (average...)

operatorD 2 contacts per day (average...)

..........

any ideas??

THANKS for you great support!!!!

[comment]

You haven't answered any of my questions.

[DukeS]

Here is rough solution to your problem. It reports Operators and their averages for contacts per day.

I modifyed data structure and now it is made of projects ( what has been done for one day and here you assign operators for this day ), operators ( here you assign contacts for operator ) and contacts.

Report shows you averages for contacts for operators ( layout must be sorted on operator ).

I don't have time to complete this solution, but maybe it will be your starting point.

HTH

Average.fp7.zip

[xlello]

You haven't answered any of my questions.

Sorry... late night posting :

Cities, actually 7, changes but seldom

I perform a find using a date range

I don't have time to complete this solution, but maybe it will be your starting point.

I really appreciate your job!!

I'm trying to better understending the process you've used... but I belive I'm in the right way

Thanks guys }:|

Edited October 27, 2006 by Guest

[xlello]

THANKS! THANKS! THANKS! I've learned a lot from you suggestions!!

So now I'm trying someting different...

In one table of my DB I've following fields:

-ID

-Date

-Operator

-City

-Location

-Customer name

-Product

-Promtion

Plus summary and calculation fields

I've made a simple report with grouped data and subtotal which gives me the total of products sold by each operator. It looks like:

OperatorA -- 12 products

OperatorB -- 15 products

...

OperatorF -- 11 products

I'm trying to add to this report a count of worked days for each operator to obtain something like:

OperatorA -- 12 products -- in 5 days

OperatorB -- 15 products -- in 7 days

...

OperatorF -- 11 products -- in 3 days

Some info...

-Operators work time to time

-Operators sell more than one product per day

-For this report I'm using just one table

-I use this script after querying for a dates range

I'm lerning a lot, but I'm still a newbie... PLEASE, HELP!! :

[xlello]

I'm trying to add to this report a count of worked days for each operator

Thanks to an Italian friend, I've found the solution ...you already gave me!!

I used "sCountDates" on Comment's file and de-flagged "running total"

It works great! :

Thank you all!!

Edited November 6, 2006 by Guest