Multiple series help please!

[lorivol]

I have a project where I'd like to create chart layouts that have multiple series. I know I need to add items to the y-axis and write a calculation, but I can't figure out how to write the calculation. Here is the situation:

I have a group of applicants to a program (all applicants)

Within this, I can search for the accepted applicants (accepted)

and the applicants who were denied (denied)

There is other information about each applicant in all of their records, for example, the state they're from.

I want a bar chart where the x-axis is count of people and y-axis is their state of residence. I can do this.

The tricky part is adding more series to the chart. I want to end up with the count of all applicants by state, all accepted by state, and all denied by state, I just can't figure out how to write a calculation that will pull up only the accepted, and only the denied applicants.

I've attached an example excel chart with what I'm looking for (where A, B, and C would be the states of the applicants in this example).

Any help is much appreciated!

[comment]

I have a group of applicants to a program

How many programs do you have?

all applicants by state, all accepted by state, and all denied by state

How is the accepted/denied status recorded? Are there any applicants that have not been accepted nor denied?

[lorivol]

Hello!

Just one program in this instance.

Accepted and denied applicants are in a field where they are labeled as "yes" or "no."

Currently every applicant has been set to yes or no, but during the acceptance process they are left blank until we make a decision.

Please let me know if you need more information. Thanks!

[comment]

So if State A has 12 applicants, 5 were accepted, 4 denied, leaving 3 undecided, you want to see 12 | 5 | 4 on the chart?

[lorivol]

Yes.

[comment]

First, I would suggest you either change the accepted/denied status field to a Number field using 1 for accepted and 0 for denied, or define a calculation field that returns such result - see also:

http://fmforums.com/forum/topic/47570-set-a-field-based-on-portal-content/page__view__findpost__p__222189

Next, define three summary fields:

• sCount = Count of ApplicantID (or any other field that cannot be empty);

• sTotalStatus = Sum of Status ("Status" being the Number field above);

• sCountStatus = Count of Status

Set the chart as follows:

X Axis: Applicants::State

Y Axis:

• Applicants::sCount

• Applicants::sTotalStatus

• GetSummary ( Applicants::sCountStatus ; Applicants::Category ) - GetSummary ( Applicants::sTotalStatus ; Applicants::Category )

Use data from: Current Found Set

Show data points for groups of record when sorted

Place the chart in a header part of a layout of the Applicants table. Before showing the chart, sort the records by State and by Status, descending.

[lorivol]

It works! Thanks so much!