Multiple If Statements 1

[talibm]

Hi All,
I have a Crustal Report (2013 that pulls data from a sql database. I have created several formulas that I need to combine calculate the number of client that have a high school deploma/ged and clients that do not have a high school diploma/ged. {Answer.QuestionUid} = 10039 and {Answer.Answer} = "8645" then "HS-GED"; {Answer.QuestionUid} = 10039 and {Answer.Answer} = "8646" then "No_HS-GED") I can get totals for these without any problem. Now to my dilemma. I have to get the total number of clients who have a HS-GED and have been employed for 1 year or two years or 3 years up to 5 years. I tried a running total to count the HS-GED adding in Evaluate {Answer.QuestionUid} = 9993 and {Answer.Answer} = "1". This is the formula for 1 year and the number changes for each year. Running total are not separating the HS-GED and the No-HS-GED. the totals are the same. I have tried using variables and several different formulas without success. I hope this is not too confusing. Any help is greatly appreciated. thanks

talib

 

[kray4660]

Maybe I am a bit confused. But you state two if statements that are for HS-GED and NO_HS-GED (which look fine) which should be part of your running totals. But the last statement {Answer.QuestionUid} = 9993 and {Answer.Answer} = "1" could not be used in the same Evaluate since it appears it refers to the same fields as the if statements (remember CR, for the most part evaluates row by row), thus {Answer.QuestionUid} could not be equal to 10039 and 9993 in the same row.

 

[talibm]

The {Answer.QuestionUid} = 9993 and {Answer.Answer} = "1" represents the number of years the client has been employed. I need to be able to combine the number of HS-GED or No-HS_GED responses with the corresponding number of years. These are the actual formulas I need to combine and then calculate. If {Answer.QuestionUid} = 10039 and {Answer.Answer} = "8645" //HS-GED Yes)// and {Answer.QuestionUid} = 9993 and {Answer.Answer} = "1" //years of employment// then 1. I need to count this for each year of employment up to 5 years. The data is from a sql database if that helps. thanks

talib

 

[lbass]

Kray4660 raises the key issue. One way of handling this is to insert a group on clientID. Then create one formula for each condition:

//{@GEDYes}:
If {Answer.QuestionID}=10039 and
{Answer.Answer}=8645 then 1

//{@Yrs1}:
If {Answer.QuestionID}=9993 then
{Answer.Answer}

Then create a running total {@GED1year} that does a distinctcount of clientID and evaluate using a formula:

Maximum({@GEDYes},{table.clientID})=1 and
Maximum({@Yrs1},{table.clientID})=1

Reset never.

Repeat for each year of employment and again for {@GEDNo}.

-LB

 

[talibm]

Hello LB,
Your suggestion worked like a charm. You're the best. Thank you very much!