Failed CASE WHEN formula in a table

[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.

[Nuke354]

Perhaps you can convert the fields to a specific data type like this:
 

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

END

CONVERT(FLOAT, [@field:SchoolYearDaysAttended])

This is what I usually do when I encounter data type incompatibility. Hope this works on yours too.

For reference, you may want to check this: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

[mdeevers]

On 10/2/2020 at 11:17 PM, Nuke354 said:

Perhaps you can convert the fields to a specific data type like this:
 

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

END

CONVERT(FLOAT, [@field:SchoolYearDaysAttended])

This is what I usually do when I encounter data type incompatibility. Hope this works on yours too.

For reference, you may want to check this: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Thanks. I will give it a try!

[mdeevers]

That didn't work, Nuke. Any other thoughts?

[NailDyanC]

Hi @mdeevers,

I tried to recreate your workflow in my end and I was not able to get the error.  I also use the same formula that you are using. The data type of the three fields is number. Below is the screen shot:
 

 

[mdeevers]

On 10/10/2020 at 4:09 PM, NailDyanC said:

Hi @mdeevers,

I tried to recreate your workflow in my end and I was not able to get the error.  I also use the same formula that you are using. The data type of the three fields is number. Below is the screen shot:
 

 

Thank you for looking into this. My solution to this issue has been to remove students who have NO SchoolYearDaysAttended (i.e., a blank field). I can't explain why, but that fixed the problem. And the more I thought of it, if that field is blank, the school would have never seen the student anyhow - so the data aren't necessary.  Thanks again for looking into this.