Conversion to find the resultant effort

[Vijay]

Hey,

I have a scenario/Confluence Table as follows:

The ask is -> I'm using a Table Transform SQL code to display these values (simple straight forward Table) .. however, the need is to display the remaining effort ..

i.e., .. for 10 Story points, if the overall progress is 50% then i need to display another column 'Remaining effort' as either 50%  or  5 Story points .. 

i have tried converting the percentage(overall progress) into number   OR   number(story points) into percentage and then tried subtracting one from another ...

BUT,

logically this is not helping ..coz it is not a direct subtraction  as in   i cant simply convert 

50% to 0.5 and then subtract it from 10 Story points coz the result will be then 9.5 which is incorrect for my ask ..

i.e., .. for 15 Story points, if the overall progress is 90% then i need to display another column 'Remaining effort' as either 10%  or  1.5 Story points ..

Also, tried something like this

((T1.'Story Points' * T1.'Overall Progress')/100 - (T1.'Overall Progress')) as 'Remaining Effort'

Still im missing something as it is not returning the expected result.

In short, how to display the remaining effort in the above scenario !!

Please help ..

[Vijay · Accepted solution]

Found a way to make it work .. 

ABS(((T1.'Story Points' * T1.'Overall Progress')/100 ) - T1.'Story Points') as 'Remaining SP',

however, still if any of you have better or alternate thoughts... please share ..

Thanks :)

[Stiltsoft support]

You may also try smth like this:

T1.'Story Points' * (1 - T1.'Overall Progress'/100)

Seems it should be working too.

[Vijay]

Great Thanks ..

just a trivial thing that  it returned the result as 1.4999999999

i have tweaked it with a ROUND:

Round(T1.'Story Points' * (1 - T1.'Overall Progress'/100), 1) as 'Remaining SP',

it looks perfect ..

Appreciate and great Thanks again :)