marrying results of two calculated fields

[stevelord]

Hi I have these two calculated fields from some metrics that had been split by gender in the raw data.  Men had one range for normal/at risk and women had another range for normal/at risk that I want to preserve.  I just want to create a new field that contains both the men and women and adds the # of men in the normal/at risk ranges to the # of women in the normal/at risk ranges to make a total # of people in the normal/at risk range.

Here are the calculated fields I have to start with:

	if(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<10, Dual('At Risk, Low (<10)',1),
	if(TestName='Body Fat (Male)' AND TestValue>=10 AND TestValue<=20.9, Dual('Normal (10-20.9)',2),
	if(TestName='Body Fat (Male)' AND TestValue>20.9 AND TestValue<=100, Dual('At Risk, High (>20.9)',3)))) as [Body Fat (Male)],
	if(TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<20, Dual('At Risk, Low (<20)',1),
	if(TestName='Body Fat (Female)' AND TestValue>=20 AND TestValue<=30.9, Dual('Normal (20-30.9)',2),
	if(TestName='Body Fat (Female)' AND TestValue>30.9 AND TestValue<=100, Dual('At Risk, High (>30.9)',3)))) as [Body Fat (Female)],

Here is a line I tried to give an idea of what I was going for (part of a resident load run after the above calculated fields are created):

[Body Fat (Male)] OR [Body Fat (Female)] as [Body Fat],

What happens is the messages from the Dual functions in the Body Fat Male/Female fields ('At Risk', 'Normal') appear only as a -1 in the listbox using the new Body Fat field and the result is put on a single line.  The frequency shown in the new Body Fat listbox does add up to the combined frequency from the Body Fat Male and Female.  I just can't get it to count and display them by/with the Normal/At Risk messages.

Help would be appreciated.  Thanks!

[stevelord · Accepted solution]

Okay, I figured it out and thought I would share with whoever else is out on this wacky noob-developer frontier with me.  Basically stuck an OR between the male and female formulas, added a few (), and modified the Dual message.:

if(((TestName='Body Fat (Female)' AND TestValue>0 AND TestValue<20)OR(TestName='Body Fat (Male)' AND TestValue>0 AND TestValue<10)),

        Dual('At Risk, Low (F<20,M<10)',1),

        if(((TestName='Body Fat (Female)' AND TestValue>=20 AND TestValue<=30.9)OR(TestName='Body Fat (Male)' AND TestValue>=10 AND TestValue<=20.9)),

        Dual('Normal (F20-30.9,M10-20.9)',2),

        if(((TestName='Body Fat (Female)' AND TestValue>30.9 AND TestValue<=100)OR(TestName='Body Fat (Male)' AND TestValue>20.9 AND TestValue<=100)),

        Dual('At Risk, High (F>30.9,M>20.9)',3)))) as [Body Fat],

Counts were validated, sum of parts = the whole, gender/values fall in the right normal/at risk message etcetera.  I have list and table boxes everywhere...

Kapow!

and my next trick shall be to use the gender-specific metric test fields to populate the gender field where gender is null for those users.  And tomorrow, the world! (or caffeine-crash )