Sorry if this question seems tedious. I am having a real time with this one. I am trying to use a formula in a table to categorize attendance rates. I have three number fields in my table (excused absences, unexcused absences and days present). Mathematically, I can calculate attendance rate with days present/(excused absences + unexcused absences + days present). That is, a student's attendance rate is the percent of days attended compared with all possible days.

I am trying to categorize attendance rates into four groups, which then has me doing a CASE WHEN statement. I am trying just one group for starters, like this:

CASE

WHEN IsNull([@field:SchoolYearDaysAttended],0)/(IsNull([@field:EXC_ABS_CUR],0)+IsNull([@field:UXC_ABS_CUR],0)+IsNull([@field:SchoolYearDaysAttended],1))> 0.95 THEN 'Excellent'

ELSE 'Not Excellent'

END

When I check, the formula editor says the formula is valid. But when I try to save the table, I get an error saying I have incompatible data. My plan is to have things get even more complicated, since I will have to do some greater than and less than work to create the four categories (i.e., WHEN ....>X AND WHEN...<Y THEN). But I would at least like to know I'm on the right path before I put all that in. Any suggestions are appreciated.

You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

## Question

## mdeevers

Sorry if this question seems tedious. I am having a real time with this one. I am trying to use a formula in a table to categorize attendance rates. I have three number fields in my table (excused absences, unexcused absences and days present). Mathematically, I can calculate attendance rate with days present/(excused absences + unexcused absences + days present). That is, a student's attendance rate is the percent of days attended compared with all possible days.

I am trying to categorize attendance rates into four groups, which then has me doing a CASE WHEN statement. I am trying just one group for starters, like this:

CASE

WHEN IsNull([@field:SchoolYearDaysAttended],0)/(IsNull([@field:EXC_ABS_CUR],0)+IsNull([@field:UXC_ABS_CUR],0)+IsNull([@field:SchoolYearDaysAttended],1))> 0.95 THEN 'Excellent'

ELSE 'Not Excellent'

END

When I check, the formula editor says the formula is valid. But when I try to save the table, I get an error saying I have incompatible data. My plan is to have things get even more complicated, since I will have to do some greater than and less than work to create the four categories (i.e., WHEN ....>X AND WHEN...<Y THEN). But I would at least like to know I'm on the right path before I put all that in. Any suggestions are appreciated.

## Link to comment

## Share on other sites

## 5 answers to this question

## Recommended Posts

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Note:Your post will require moderator approval before it will be visible.