Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 )
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 )