Part of my app deals with OKRs – think of them as nested tasks if you are not familiar.
Each parent has a number of children.
Each child has a confidence score set based on progress and elapsed time.
I wish to show a calculated confidence score the the parent based on the average confidence of the children
(slightly simplified, but covers the important points).
What I tried to do
Each child has a confidence score built from an if/then statement something like
if progress>= 100% confidence = 100.
If deadline > today confidence = 0
if progress >= Elapsed time (do some maths) etc etc etc
End result is a number 0-100
I wanted to use a rollup to average those scores. However, it seems that if/then columns are treated as text and it doesn’t work
I’ve been trying to work around this using maths functions, but either I don’t get the syntax, or those are fairly basic.
I’ve read everything I can think to read on this and can’t think what else to try. Maybe I am too stuck in the solution I had in mind. Anyone have thoughts? I don’t really want to change the way the formula works out the score as users are used to what the numbers mean.
Just multiplying the result of your if-then-else column by 1 should do the trick.
1 Like
I really wish I’d given up and asked two hours ago now! Perfect. I’m going to leave you in peace now. Thanks
Just curious, does your IF column have any paths where it returns text or a blank instead of a number? It’s not always the case, but I find that if the output of an IF column is a consistent type across all paths (for example, it always returns a number), then the IF column will assume they same ‘type’, but if any one of those paths returns text or a blank, then the IF column will always default to a ‘text’ type. So if the ELSE portion of an IF column is blank, then it will be a text type of result because blank is a possibility.
1 Like
It was originally numbers or empty, but I tested by replacing the empty result with 99 just to rule that out. Same thing: No maths functions offered on the rollup.
1 Like
OK, good to know. Maybe it’s the fact that you are manually typing in numbers, so they remain as ‘text’ instead of ‘numeric’, whereas if you always returned results from numeric columns, then I think the IF result would be numeric. Something to keep in mind though for the future.
Interesting. I might check that all the forms are set to numeric not text. Could be it.
1 Like