Project and Portfolio Management Practitioners Forum
cancel

Auto Calculated Field

Highlighted
Ryan Grobler
Super Contributor.

Auto Calculated Field

Hi All

I am wanting to add a new field into Project Risks which will be calculated based on 2 other fields in the Risk

Currently we have 2 fields: Impact (Validation: Low, Medium, High) and Probability (Validation: Low, Medium, High)

I need to create a new field which looks at the above fields and then calculates a number:

If both validations are low, the score is 1.
If one has is low and the other is medium, the score is 2,
If one is High and the other Low, the score 3
If one is High and the other Medium, the score is 6
If Both are high the score is 9.

Anyone know how I would create this field?

Thanks in advance!

Ryan
23 REPLIES
Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Hi Ryan,

You can use the following SQL in the rule
taking Impact and Probability as dependencies and result as new field.

select decode(,'High',Decode(,'High',9,'Medium',6,'Low',3),'Low',Decode(,'Low',1,'Medium',2) from dual

Thanks
Shravan kumar.K
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hey

Thanks for the code

SO I've created a new field in the 'Request Header type' called 'matrix' and given in a validation of 'numerical'.

Within the Request type I've gone to the Rules tab and created a new rule - (Screenshot attached)

but the value works out at zero.

Should the text "decode(KNTA_RISK_IMPACT_LEVEL,'High' " - should "high" be the code value; the meaning field or the description field?

Thanks!
Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Hi Ryan,

You should use '[REQ.VP.KNTA_RISK_IMPACT_LEVEL]' instead of just token name in the rule.


Thanks
Shravan kumar.K
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hi Shravan

Does this look right?

Select decode(REQ.VP.KNTA_RISK_IMPACT_LEVEL,'High',Decode(REQ.VP.KNTA_PROBABILITY,'High',9,'Medium',6,'Low',3),'Low',Decode(REQ.VP KNTA_PROBABILITY,'Low',1,'Medium',2) from dual

Sorry - not a SQL programmer - just work in the PMO here :)

thanks!!
Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Right, It will work.
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hi Shravan

Am getting an error message now

I've attached the screenshot - have I used "high" when i need to use "high [66%-100%]"?
Mahen M
Acclaimed Contributor.

Re: Auto Calculated Field

I think you missing the square brakets and the quotes in the Token

Regards,
Mahendran M
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

I tried using this:

select decode'[KNTA_RISK_IMPACT_LEVEL','High'],Decode'[KNTA_PROBABILITY,'High',9,'Medium',6,'Low',3,'Low'],Decode'[KNTA_PROBABILITY,'Low',1,'Medium',2]' from dual

but it won't accept this - have i messed up the code completely?
Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Put the following code as it is and try to see.

Select decode('[REQ.VP.KNTA_RISK_IMPACT_LEVEL]','High',Decode('[REQ.VP.KNTA_PROBAB
ILITY]','High',9,'Medium',6,'Low',3),'Low',Decode('[REQ.VP KNTA_PROBABILITY]','Low',1,'Medium',2)),
decode('[REQ.VP.KNTA_RISK_IMPACT_LEVEL]','High',Decode('[REQ.VP.KNTA_PROBAB
ILITY]','High',9,'Medium',6,'Low',3),'Low',Decode('[REQ.VP KNTA_PROBABILITY]','Low',1,'Medium',2)) from dual
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

That drives an error message:

Rule SQL has Invalid Token(s). Tokens allowed are Systems tokens [SYS.*], Application Server Tokens [AS.*] and tokens of fieldws defined in the Rule Dependencies List. (KNTA-10561)

What does this look to, the Meanings or the Descriptions?

I've attached the validations this is refering to - incase I've not given the correct info :)
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Probability Validation
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Impact Validation
Mahen M
Acclaimed Contributor.

Re: Auto Calculated Field

Ryan,
Usually we will have the Code of validation in caps... but it is not 100% necessary for the code to be in Caps.

'[REQ.P.TOKEN_NAME]' refers to code
'[REQ.VP.TOKEN_NAME]' refers to meaning.

you are comparing two validations with the visible parameters.

Please make sure that the field tokens which you use in SQL Query is there in Dependencies list.

Your Validation or SQL needs change.

Regards,
Mahendran M
Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Please try this code

Select decode('[REQ.VP.KNTA_RISK_IMPACT_LEVEL]','High',Decode('[REQ.P.KNTA_PROBAB
ILITY]','HIGH',9,'MEDIUM',6,'LOW',3),'Low',Decode('[REQ.P.KNTA_PROBABILITY]','LOW',1,'MEDIUM',2)),
decode('[REQ.VP.KNTA_RISK_IMPACT_LEVEL]','High',Decode('[REQ.P.KNTA_PROBAB
ILITY]','HIGH',9,'MEDIUM',6,'LOW',3),'Low',Decode('[REQ.P.KNTA_PROBABILITY]','LOW',1,'MEDIUM',2)) from dual
Mahen M
Acclaimed Contributor.

Re: Auto Calculated Field

Sharvan, The cases are different in the two validation, Will it work?

Regards,
Mahendran M
Mahen M
Acclaimed Contributor.

Re: Auto Calculated Field

Yep... this way of coding works, Thanks

Regards,
Mahendran M
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Doesn't seem to work either:

Below are the Codes and Meanings, I've tried to substitute in the items, but can't get it to work either:

Probability
Code Meaning
HIGH High [66-100%]
MEDIUM Medium [33-65%]
LOW Low [0-32%]

Impact
Code Meaning
LEVEL_ONE HIGH
LEVEL_TWO MEDIUM
LEVEL_THREE LOW

Thanks for the help on this one - if it doesn't work I'm happy to raise a support ticket :)

Ryan

Shravan Kathuri
Honored Contributor.

Re: Auto Calculated Field

Hi Ryan,

Can we have net meeting so that i can explain.

Thanks
Shravan kumar.K
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hi Shravan

Sure can do! Not sure if we have a standard web meeting tool here though - anything you use in particular?

Ryan
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hi Shravan

Do you want to ping me an email re the net conference?

its ryan.grobler@hiscox.com

Rgds

Ryan
Erik Cole_1
Outstanding Contributor.

Re: Auto Calculated Field

Shravan, your decode doesn't appear to be handling the case where REQ.VP.KNTA_RISK_IMPACT_LEVEL is MEDIUM...?

You could also use a CASE statement - longer, but easier to follow the logic IMHO:

Select Case
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '1'
When ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') Then '9'
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_TWO') OR ('[REQ.P.KNTA_PROBABILITY]' = 'MEDIUM' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '2'
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') OR ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '3'
When ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_TWO') OR ('[REQ.P.KNTA_PROBABILITY]' = 'MEDIUM' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') Then '6'
Else 'ERROR' End as P,
Case
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '1'
When ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') Then '9'
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_TWO') OR ('[REQ.P.KNTA_PROBABILITY]' = 'MEDIUM' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '2'
When ('[REQ.P.KNTA_PROBABILITY]' = 'LOW' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') OR ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_THREE') Then '3'
When ('[REQ.P.KNTA_PROBABILITY]' = 'HIGH' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_TWO') OR ('[REQ.P.KNTA_PROBABILITY]' = 'MEDIUM' AND '[REQ.P.KNTA_RISK_IMPACT_LEVEL]' = 'LEVEL_ONE') Then '6'
Else 'ERROR' End as VP
From dual
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

Hi Eric

Code works! Thank you very much!
Ryan Grobler
Super Contributor.

Re: Auto Calculated Field

wokring now!