Standard Deviation does not add up!

[Rramjet]

I am messing myself (and you) around with edits to this this post but...)I am trying to do Std Dev by hand because the summary function seems to get it wrong.

As you all know :

StdDev = sqrt(Sum((score- mean)squared)/N-1)

Prospectively I can operationalise this by creating sequential variables to represent each phase of the above StdDev formula, as follows:

var_1 = score minus mean (calc. unstored)

var_2 = (score minus mean)squared ...or (var_1 * var_1 - as FMP does not have a sqr function)(calc. unstored)

var_3 = Sum(var_2) (global)

var_4 = var_3/N-1 (calc. global)

var_5 = sqrt(var_4) (calc. global)

var_1 (score minus mean) works perfectly, as does Var_2. But I cannot get var_3 to work.

I have tried:

Var_3 = Total var_2 (Summary field)

Var_3 = Sum(Var_2) (calc. global)

Any suggestions would be appreciated.

Rramjet

Edited August 25, 2009 by Guest

[Rramjet]

In fact none of the above works :

I have a portal... (Number of sessions). I can get the total number of sessions for each record (via either a summery or calculation field).... but from then on I just can't get anything to work properly. I am stumped for now...

[comment]

the summary function seems to get it wrong.

Could you post an example showing this? Preferably a simple one, that can be checked by hand.

[Rramjet]

Hi Comment -

Thank you for your interest.

Perhaps I should expand on my database first and exactly what I am trying to achieve (because I think it has something to do with the fact that calculation fields working from portals behave extremely weirdly) then provide an example.

I have three tables related by (autoenter serial number):

1. Activities (Parent)

2. Providers (Child)

3. Sessions (Child)

I have two (primary) layouts:

1. Activities Record

2. Provider and Session Record

The Activities Record LO contains such fields as , , , , , etc.

The Provider and Sessions Record LO has two portals:

1. Provider details

2. Sessions details

The Sessions details portal (the one I am working with for this example) contains fields from the Sessions table such as

(autoenter calculation: Get(PortalRowNumber))

This structure allows, for each Activity_ID (from the Activities table & LO), the user to record a number of unique sessions. For example:

On the Activities Record LO might = "Treatment" and then in the sessions portal on the Provider and Sessions Record LO

the user can record how many episodes of treatment (Date, time, etc) are conducted.

What I need is to calculate (for example) for each (eg; Treatment, Education/Training, etc) and overall,

the Mean number of sessions and the StdDev.

PART 1 (skip to Part 2 below if you know the following cannot work because it is not calculating on the "direct" Sessions layout directly from the Sessions table... it is instructive of FMP behaviour though and a new function is discovered!)

So (referring back to the original post above):

To begin- on the P&S R LO: Mean = (Sum of all (Sessions_IDs))/N. To operationalise this in FMP we must use a series of variables:

One must first know the total number of sessions for each , so one could:

= Summary Count of Session_ID (and this works fine).

But then there is a problem. This summary field cannot then be used in further calculations!

(It CAN, but provides useless results. Eg:

Sum_N_Sessions = (unstored calculation Sum (N_Sessions) = N_Sessions! (Hugh?)

Sum_N_Sessions = (global calculation Sum (N_Sessions) = correct value BUT if the user enters another session into any record,

Sum_N_Sessions reverts to the total number of the sessions of the focus record (which is is effect N_Sessions) ...globally...

How do we workaround this?

In the past (NOT with portals) I have simply defined another field as a calculation or autoenter number field that simply = ,

and as this is NOT a summary field it may be used in subsequent calculations.

However, WITH portals this creates some VERY weird behaviour in FMP.

For example:

If I define = (unstored calculation; Count(Sessions_ID)) then a "1" is entered into the field by FMP - and a 1 does NOT reflect

the number of sessions in each portal (as the summary field above does correctly).

If I define = unstored autoenter; Count(Sessions_ID)) then the field becomes blank.

If I resign myself to the summary filed and use the standin variable method then I can get:

= Summary Count of Session_ID and

= (and this works just fine...but then ...)

I want to Sum these values across all records (Activity_ID)

= (unstored calculation Sum(N_Sessions_for_Calc)) and this actually provides a SQUARE of N_Sessions for each record!

FMP seems to be multiplying * because if I take

Sum_N_Sessions = (unstored calculation Sum (N_Sessions) = N_Sessions! (Hugh?)

Sum_N_Sessions = (global calculation Sum (N_Sessions) = correct value BUT if the user enters another session into any record,

Sum_N_Sessions reverts to the total number of the sessions of the focus record (which is is effect N_Sessions) ...globally...

Weird!

As you probably will have guessed by now, instituting an FMP summary field for "Average" or StdDev" on (well of course this is impossible as it is a summary field itself!) or

gets me wildly anomalous results.

The problem seems to be that FMP is inextricably linking the two variables and and using them both to create a result (don't ask me how, the results show a function that I have yet to discover the logic of (although there will be one no doubt)

(except for the SQUARE function ...and you thought FMP did not HAVE one of those - well I have discovered a way for it to do it... :

So... the upshot is I have played with various field types using the logical structure in my original post, but nothing works as it (on the face of it) should.

I have even created a cut and paste loop script which goes to the portal row containing the highest Session_ID value, then copying and pasting that value to a standin variable for each record and then trying to calculate from there, but it still does not work. Not only that but SetField does not work in lieu of copy and paste!

Here I am frustrated. Stumped. Stopped in my tracks.

PART 2

Now, on the Sessions LO (which of course will be much more friendly to calculations from the sessions table :

I still want a mean and StdDev...

So first I need a count of the number of sessions for each Activity_ID.

= (summary = Count(Session_ID)) merely returns the total number of records and makes no distinction by Activity_ID. So then...

= (unstored, = GetSummary(N_Sessions; Activity_ID)) returns a correct value, but "globally" for each record by Activity_ID.

So now... I still need to sum the number of sessions (to eventually get the mean number of sessions by Activity_ID) but I cannot do it from because that provides a grand total that no break field is going to sort out because it is weighted by number of sessions...(if you know what I mean...)

So logically I need a function to sum by a the break field (Activity_ID) so that I have:

Instead of (for example):

=1

= 3

= 3

= 3

=2

= 2

= 2

=3

= 4

= 4

= 4

= 4

I need:

=1

= 3

=2

= 2

=3

= 4

Now I am (despite my "intermediate" forum status) a relative novice here and am stumped as to how to proceed from here.

Remember I want the overall mean and stddev for number of sessions by Activity_ID, but I also want eventually to break that down by Treatment Category (but I guess the solution to the first will give me the second).

I am working on it...there must be a way... but as I say... "novice" I am in this respect.

Any suggestions?

[comment]

IIUC, one activity can have many sessions and each activity has a Consultation_Type.

What I need is to calculate (for example) for each (eg; Treatment, Education/Training, etc) and overall,

the Mean number of sessions and the StdDev.

I don't understand the meaning of "mean number of sessions" in this context:

Each activity has a known number of sessions - obtainable by Count (Sessions::Activity_ID).

Each Consultation_Type also has a known number of sessions - it is the sum of the session counts in all activities of the same type.

I don't see anything here that can be meaningfully averaged.

[Rramjet]

Perhaps I did not explain the setup as well as I could have.

My primary concern is that I have a Sessions table (containing Session_ID, among others) which is accessed via a portal from a Layout that is linked to the Activity table (containing Activity_Id & Consultation_Category, among others).

The Sessions portal displays the number of sessions (of treatment, or training, or health education, etc) related to each Activity_ID). For example:

Activity_ID = 1 (on the Activities layout) is an episode of treatment with 6 sessions (recorded in a portal on the Sessions layout) conducted (on various dates and specified times).

Activity_ID = 2 is an episode of training with 4 sessions conducted (on various dates and at specified times).

Activity_ID = 3 is another treatment activity with 4 sessions... and so on.

In one year we could then have 120 treatment activities, 67 training activities, 98 education activities and so on.

However, each individual treatment, training, etc activity might have a different number of session related to it.

I then need to report two things:

1. the overall mean number (& StdDev)of sessions conducted (no matter what the activity) over the year (and it is made more complex by having to report mean session lengths (in mins), number of activity providers, number of activity recipients, etc)...but let us just deal with one (number of sessions) and the rest should logically be the same...

2. I need to report the mean number (& StdDev) of sessions for each activity category (treatment, education, etc)...again duration, number of providers & number of recipients need the same stats reported PLUS other stats that compare PLUS I have a separate "Providers" portal (and table) so I can report stats by individual clinicians, educators, etc...whether the sessions were group or individual (provider or recipient) presentations & so on... but again, let's stick to the number of sessions ...

So, I need to "flip" between layouts to display the various calculation fields that go into the stats formulas (mean and StdDev being just the beginning...)

Now...FMP does not seem to handle that very well...or at all really. Maybe it can, but I can't get it to work...

To reiterate (at the risk of being overwhelming...)

FMP summary "average" does NOT provide a meaningful figure to represent a mean number of sessions (working from either the straight Session_ID or a calculation of Count(Session_ID) and I certainly cannot get meaningful SUM(Session_Id) working. I thought GetSummary with Activity_ID as the break field might help (and it does...sort of... on the Sessions layout - getting the info directly from the Sessions table) but I need to go back to the Activities Layout (with the portal) to Sum the number of sessions... if that makes sense...

There must be a logical structure that escapes me...it has to be there, but the difficulty is shown up by your questioning about whether there is any meaning in my request at all...

I hope the above has made my needs clearer - if not...can I post my database with dummy data anywhere so you can view it yourself?

Again, I appreciate your interest,

Rramjet

[comment]

I am afraid I don't follow you at all. I am not familiar with the terms you are using, e.g.:

"the overall mean number (& StdDev)of sessions conducted"

As I said earlier, you cannot average the count of a population. You CAN average session length, because it has both a SUM and a COUNT. The number of sessions is COUNT only.

can I post my database with dummy data anywhere

You can attach a (zipped) file right here. I suggest you keep it as simple as possible, and indicate clearly the required result.

[Rramjet]

Hi Comment -

I have attached a zip file of my database. I am currently building it so it is not yet as complete and polished (elegant) as it will (hopefully) end up :

You will notice two primary layouts - Activity and Session...the others (Provider and Test) are there for reference purposes (Test is in fact the link to the Sessions table...) while the report layouts are ...self explanatory.

Navigation between the two primary layouts is via the blue arrow in the top right of each layout (Tooltips abound).

On the Activity layout you can see a table structure with 10 or so fields for data entry, including Activity_ID (autoenter serial number) and the Consultation_Category variable (This is actually a conditional value list linked to consultation type).

On the Sessions layout you will see two portals (Provider details and Session details).

Here (and on the Test layout) there are a mess of fields to the right of the layout proper and these I have been messing around with to try and solve my problem (well...some of the fields have gone but the labels remain!).

The data entry proceeds from the Activity layout to the Sessions layout (with the date from the Activity record autoentering as the date of the first session)

What I need: (and I'll concede your observation concerning averaging the number of sessions over the population - it IS or should be possible but is perhaps slightly meaningless because of the different Consultation Categories...)

Nevertheless: Primarily, at the base level, I need, for each Consultation Category, a Mean number of sessions (with a StdDev) .

I also want, if possible, the statistics to be dynamic (unstored if you like), so that when other records and sessions are entered, the statistic automatically updates.

That is my base requirement.

From there things become more complex, as I need also to report statistics for duration, number of providers and recipients, and then there is the Provider information to report on similarly - and in combination with the Sessions info...

...but if I can solve the Mean and StdDev problem (No. Sessions by Consultation Category), logically the rest should follow... (forever the optimist. Ha! :

Please feel free to chastise me if I have missed something very basic here...

Thanks,

Rramjet.

Bushfire.zip

[Rramjet]

Okay...

I have got the overall mean and StdDev working! :

In the Sessions table I created:

Valid_Sessions = Case(IsValid(Session_ID); 1) (unstored calculation)

Then in the Activity table I created:

Number_of_Valid_Sessions = Sum(Sessions::Valid_Sessions) (unstored calculation)

Then...

Mean (Summary) = Average of Number_of_Valid_Sessions

StdDev (Summary) = Standard Deviation of Number_of_Valid_Sessions

Now... all I have to do is work out how to create variables to do a Mean & StdDev by Activity category...

[Rramjet]

...and now I have the categories working

In the Activity table I created:

Tx_Indicator (unstored calculation) = Case(Consultation_Type = "Treatment"; 1)

Then,

Tx_Number of Valid_Sessions (unstored calculation) = If(Tx_Indicator=1; Number_of_Valid_Sessions; "")

Remember Number_of_Valid_Sessions = Sum(Sessions::Valid_Sessions)

Then it was a simple matter of...

Tx_Mean (Summary) Average of Tx_Number_of_Valid_Sessions

Tx_StdDev (Summary) Standard Deviation of Tx_Number_of_Valid_Sessions

Then to get the N for each category it was

N_Tx (Summary) Count of Tx_Indicator.

I repeated for each Activity category et voila!

Now I have the logical structure:

1. Create a field in the table from which the portal derives (Sessions) and set to = 1 for valid cases [Valid_Sessions = Case(IsValid(Session_ID); 1)].

2. Create a total of the Valid_Sessions in the table (Activity) associated with the layout the portal is displayed on [Number_of_Valid_Sessions = Sum(Sessions::Valid_Sessions)]

3.Create a category indicator field in the table (Activity)associated with the layout the portal is displayed on [Tx_Indicator = Case(Consultation_Type = "Treatment"; 1)

4. Create a category number of sessions field in the table (Activity) associated with the layout the portal is displayed on [ Tx_Number_of_Valid_Sessions = If(Tx_Indicator=1; Number_of_Valid_Sessions; "")]

5. Create category Mean and a StdDev field using FMP native summary field functions (Average and Standard Deviation of Tx_Number_of_Valid_Sessions).

6. To get the number of records in the (in this example) Treatment category make Tx_N = (Summary) Count of Tx_Indicator.

7. Repeat steps i - 6 for each category.

Now I have created unstored means and standard deviations that update whenever new records and/or new sessions to existing records are created.

Using this logic I should be able to work from here on creating fields for reporting the other fields in this Sessions portal and the Providers portal plus using the fields in various formulaic combination to provide other statistics of interest.

This has been an arduous and frustrating journey of discovery for me and I am sure that as soon as I leave this post I will run into unforseen problems, however, I have solved my primary concern - to get autoupdating means and standard deviations by category while using portals and the ability to use the fields generated along the way in other calculations directly (I am sure soon I will find the GetSummary and Evaluate functions also helpful).

Thanks for your patience.

And a BIG thanks Comment - if it were not for his involvement I would not have repeatedly gone back to investigate as I answered his questions and perhaps remained stuck in my original logic trap (suggestions still welcome of course... :

Cheers to all.

Rramjet.

[comment]

I think you could make this a lot simpler by producing a report from the Sessions table, using summary fields (in the same table) and sub-summary parts.

For "live" on-screen summaries, I'd suggest you add a table of Types (as parent of Activities), and summarize the Sessions from there. That should save you some calculation fields, I think.

[Rramjet]

Yes, that's true (why not produce a report from the Sessions table), but I want a report that also displays summary stats for other fields (Duration, Providers, recipients, etc) on the same layout as the sessions stats... a sort of summary summary report if you like. To do as you suggest and fulfil my requirements for this, some information would need to be displayed in the header and some in the parts and then neat formatting becomes a problem (but perhaps you are right even so... I admit I did not investigate this option as carefully as I might have done)

Your extra table idea is interesting but it could be like a chicken and egg scenario... save creating fields in one table only to require them in another... but worth investigating nevertheless.

Thanks for your input Comment.

In humble appreciation,

Rramjet.