Expand my Community achievements bar.

SOLVED

Help with a Calculated Column

Avatar

Level 4

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!

1 Accepted Solution

Avatar

Correct answer by
Level 4

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.

View solution in original post

3 Replies

Avatar

Level 3

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")))))

Avatar

Level 4

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!

Avatar

Correct answer by
Level 4

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.