Problem with IF/Then logic

[#1]

I am try to create an expression that will look at an employees status (Active (A) or InActive (I)) and based on their status count their ethnicity.  I am trying to use a simple IF/Then/Else statement, but it isn't recognizing the criteria:

If([IND]='A',Count([Ethnic]),'1')

The results are displaying the '1'.  If I change the = to not equal (<>), then the results are counting ALL employees by their ethnicity.  I ultimately want a count (by ethnicity) of all active employees and then a count (by ethnicity) of all inactive employees

If([IND]='A',Count([Ethnic]),'1')

If([IND]='I',Count([Ethnic]),'1')

Would I be able to do the same type of logic checking if the employee has a term year?

If([Term Yr]<0,Count([Ethnic]) to count active employees

If([Term Yr]>0,Count([Ethnic]) to count inactive employees

My source is an Excel file, if that makes a difference.

Thanks in advanced

Mauricio

[Jason_Michaelides · Accepted solution]

Instead of an IF statement try using set analysis:

Count({<IND={A}>} Ethnic)

and

Count({<[Term Yr]={"<0"}>} Ethnic)

Hope this helps,

Jason

[#3]

Thanks Jason for the quick response.  It worked like a charm!

Mauricio

[#4]

Thanks for the quick response Jason.  It worked like a charm

Mauricio

[#5]

Jason,

Thanks again for your help.  Is there a way for me to take the set analysis function

Count({<IND={A}>} Ethnic)

and use it to get the percentage of ethnicity based on the total of all ethnicity?

Example

Asian     432     43.7%

Black     196     19.8%

Hispanic 360     36.4%

  TOTAL  988   100.0%

Thanks again for your help.  Did you take an advanced class to learn these tricks or was it just learning by doing?

Thanks again

Mauricio