Jump to content

Summarizing according to multiple criteria


This topic is 7579 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
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.


Link to comment
Share on other sites

This topic is 7579 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

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