Jump to content
Sign in to follow this  

Summarizing according to multiple criteria

Recommended Posts

I'm in need of creating a layout which would contain summaries for each combination of values in several fields. i.e. field1 could be A, B, or C. field2 could be P, Q, or R. Field3 can be X, Y, and Z. I'd like to create a report where for each value in Field1, it displays a chart of field2 values against field3 values...

Maybe I'm making it too confusing...

The nuts and bolts of it is that I want to create a formula which would do the following:

foreach (value in field1)

count all records for which field2 == [P] AND field3 == [Y]

(P + Y being examples)

I can currently do this by making subsummaries of subsummaries, etc., but the report comes out to be 100 pages long. Is there any way to create a hybrid where I could have the body be a chart, but under a sub-summary?

I've tried GetSummary(), but I don't think it buys be anything. I've looked into Aggregate functions, but I can't specify qualifications on the Count() function...

Any help would be appreciated.

- K

Share this post

Link to post
Share on other sites

You can use aggregate functions, but you will need to create fields for each combination:

Test1 (calculation, number) = (field2 = "P" and field3 = "Y") <-- or whatever conditions or combinations you need to add up.

A constant field:

Constant (calculation, number, indexed) = 1

A self relationship:

"Rel" with Constant <--> Constant

TotlTest1 (calculation, number) = Sum(Rel::Test1)

This will sum all the records in the file regardless of the found set. If you need to limit the records totaled, you must do so by modifying the self relationship.

OBTW, I think surveys are one of the hardest things to do well in FM. Purpose built survey programs are better at this.


Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.