Structure, total underestimation formula enhancement

[Иван Беляков]

Good day.

Is it possible to enhance the Structure's formula below to change % text's color based on the value? I.e. If 0% - green, between 0% and 20% - orange, above 20% - red?

WITH Original_Cost =
SUM { original_estimate } :

WITH Factual_Cost =
SUM { timespent + remaining } :

WITH Overspending =
Factual_Cost - Original_Cost :

IF Original_Cost > 0 :
Overspending / Original_Cost

[Dima Kuzmin _ALM Works_ · Accepted solution]

Hi Ivan,

Dmitrii with ALM Works here.

Check this out:

WITH Original_Cost =
SUM { original_estimate } :
WITH Factual_Cost =
SUM { timespent + remaining } :
WITH Overspending =
Factual_Cost - Original_Cost :

WITH Ratio = ABS(ROUND(Overspending / Original_Cost, 1)*100):

if (

not defined (Original_Cost) or Original_Cost = 0;

"-";

Ratio = 0;

CONCAT("{panel:bgColor=green}", Ratio,"{panel}");

Ratio > 0 and Ratio < 20;

CONCAT("{panel:bgColor=orange}", Ratio,"{panel}");

Ratio > 20;

CONCAT("{panel:bgColor=red}", Ratio,"{panel}")

)

CONCAT function is used to colour the RATIO. ABS is used to get rid of negative values (in case Factual Cost < Original Cost). The result is calculated in percent, because of ROUND. If Original Cost is not defined or equals 0, you will see "-", cause you cannot divide by 0.

Don't forget to select "Wiki Markup" in "Format".

Kind regards,

Dmitrii Kuzmin

[Иван Беляков]

Thank you Dmitrii.

It worked, but only partially though. With "original" formula the upper hierarchy levels summed up the values, while with "enhanced" version it does not.

Please see attached screenshot where blue marks "enhanced" version, and red marks "original" one.

[Dima Kuzmin _ALM Works_]

Hi Ivan,

The formula calculates the values for all tasks, but because of how ROUND function was configured in my example, 4.3 turned to 0.

Here the formula rounds value to the 1st decimal place:

ABS(ROUND(Overspending / Original_Cost, 1)*100):

Customizing the formula to round value, let's say, to the 4th decimal place should provide the results you need:

ABS(ROUND(Overspending / Original_Cost, 4)*100):

Kind regards,

Dmitrii Kuzmin

ALM Works

[Иван Беляков]

It worked, thank you. Is there any way to put "%" after the numbers?

[Dima Kuzmin _ALM Works_]

Yes, you can add

, "%",

after Ratio in each condition.

Kind regards,

Dmitrii Kuzmin

ALM Works

[Иван Беляков]

Thank you