Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
stevelord
Specialist
Specialist

marrying results of two calculated fields

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!

1 Solution

Accepted Solutions
stevelord
Specialist
Specialist
Author

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 )

View solution in original post

1 Reply
stevelord
Specialist
Specialist
Author

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 )