Help with a Calculated Column

[TravisGrogan1]

I'm trying to setup a calculated column in a View that will check the "Estimate" points for our stories list and will assign a "size" to it. Clearly I have an issue in my expression because it's not working, but here's what I've got so far:

displayname=Size
textmode=true
valueexpression=IF({estimate}<=1, CONCAT("Extra-Small (", {estimate}, ")"), IF({estimate}<=2, CONCAT("Small (", {estimate}, ")"), IF({estimate}<=3, CONCAT("Medium (", {estimate}, ")"), IF({estimate}<=5, CONCAT("Large (", {estimate}, ")"), IF({estimate}<=8, CONCAT("Extra-Large (", {estimate}, ")"), "False")))))
valueformat=HTML

Ideally this will return a value that looks like this:

Extra-Small (1)

Small (2)

Currently it's returning this in every row:

Extra-Small ()

Any help is very much appreciated!

[nryan2]

I've done something similar using a custom field "Count" where I used ranges for each size. Example: If count is greater than 0 and less than 1 returns XS.

If it helps, here's the text code (beware of extra spaces that will need to be removed if you copy it)

valueexpression=IF({DE:Count}>=0&&{DE:Count}<=1, CONCAT("Extra-Small (",{DE:Count}, ")"), IF({DE:Count}>1&&{DE:Count}<=2, CONCAT("Small (",{DE:Count}, ")"), IF({DE:Count}>2&&{DE:Count}<=3, CONCAT("Medium (",{DE:Count}, ")"), IF({DE:Count}>3&& {DE:Count}<=5, CONCAT("Large (",{DE:Count}, ")"), IF({DE:Count}>5&&{DE:Count}<=8, CONCAT("Extra-Large (",{DE:Count}, ")"), "False")))))

[TravisGrogan1]

Thank you for responding. Looking over your code vs. mine, I think my code is correct. The issue I'm running into must have something to do with the "Estimate" field in particular.

I simplified my expression down to just this:

displayname=Size
textmode=true
valueexpression=IF({estimate}<=1, "TRUE", "FALSE")
valueformat=HTML

I now see "TRUE" in every row regardless of the value of the estimate field. I have items in my story list that have values of .5 up to 3 and they all just say "TRUE".

If anyone has any ideas, I could certainly use the help!

[TravisGrogan1 · Accepted solution]

Looks like I was correct. You cannot use the "Estimate" field in this manner. Instead I had to do math based on the workRequired field instead. Final code that is working looks like this:

displayname=Size
textmode=true
valueexpression=IF((({workRequired}/8)/60)<=1, CONCAT("Extra-Small (",(({workRequired}/8)/60), ")"), IF((({workRequired}/8)/60)<=2, CONCAT("Small (",(({workRequired}/8)/60), ")"), IF((({workRequired}/8)/60)<=3, CONCAT("Medium (",(({workRequired}/8)/60), ")"), IF((({workRequired}/8)/60)<=5, CONCAT("Large (",(({workRequired}/8)/60), ")"), IF((({workRequired}/8)/60)<=8, CONCAT("Extra-Large (",(({workRequired}/8)/60), ")"), "False")))))
valueformat=HTML

8 is the number of hours per story point for this team. workRequired is stored in minutes, so dividing by hours and then by 60 gets you to points.