Is there a way to prioritize projects within individual programs?

[NickVa1]

Hi WF Community,

Does anyone know if there's out of the box functionality to prioritize projects within individual programs, or can you only prioritize across all programs?

Thanks.

Nick

[Doug_Den_Hoed__AtAppStore · Accepted solution]

Hi (again) Nick,

I'd recommend you create a calculated custom parameter (see below), which leans on our Project Priority Indicator (PPI) concept within our Just In Time Resourcing (JITR) solution.

For background, the Portfolio Optimizer we were discussing in your other thread today keeps an ordinal integer called Portfolio Priority on each Project that is ranked within the Portfolio Optimizer, lowest number (1) being Most Important Project (within that Portfolio). Each Project, though, can independently be tied to any (or no) Program with each Portfolio, defeating the concept of priorities by Programs...particularly if you choose to repeat Program names across Portfolios.

Given the above, I suggest you create a "Project Priority Indicator" calculated parameter at the Project level that interrogates the Portfolio and Programs using a series of IF statements to enforce the Project Priority, assigning "weights" to Portfolios, Programs, and (if you're able to get it going) the Portfolio Optimizer's Portfolio Priority, resulting in something similar to this pattern (assuming you have less than 100 Projects per Portfolio/Program; otherwise, multiply each number by 10 or more accordingly):

=IF("Portfolio Name"="Portfolio X",100000,IF("Portfolio Name"="Portfolio Q",110000,IF(etc,etc,990000)))+IF("Program Name"="Program J",1000,IF("Program Name"="Program E",1100,IF(etc,etc,9900)))+IF(ISBLANK(Portfolio Priority),99,Portfolio Priority)

As a result, the PPI for the most important Project would then be 100000 (for the top Portfolio) + 1000 (for the top Program) + 1 (for the top Project within that Portfolio) = 101001, and the least important Project would be 990000 (for a missing Portfolio) + 9900 (for a missing Program) + 99 (for a non-Portfolio Optimized Project) = 999999

As a personal preference, I sometimes deduct those from 1000000 so that the highest priority also has the highest number (898999), but either works, mathematically.

Regards,

Doug

[NickVa1]

Doug, great custom solution suggestion! I have a meeting next week with the parties that requested this. I'll see if this could meet their needs. If so, I'll try it out and advise.

Thanks again!

[NickVa1]

Hi Doug -

I'm trying to test out the custom calculated field you suggested, using the IF statement above, but I'm getting an error "This is an invalid custom expression...".

Here's what I have, sans our actual portfolio and program names (which I have substituted correctly on my side):

=IF("Portfolio Name"="ABC",100000,IF("Portfolio Name"="DEF",110000,IF("Portfolio Name"="GHI",130000)))+IF("Program Name"="Program J",1000,IF("Program Name"="Program E",1100,IF("Program Name"="Program A",9900)))+IF(ISBLANK(Portfolio Priority),99,Portfolio Priority)

I tried removing the quotes "" from the start and end of the field names (Portfolio Name and Program Name), but that didn't fix things. I also read this IF Statements article, and everything seems on the up and up with what you provided.

Any idea what might be missing?

Thanks.

Nick

[NickVa1]

Hang tight, Doug, if you don't want to invest time in advising.

I broke it down to just the Portfolio Name scoring part of the IF statement, and I have it working. I'll slowly add more components to the IF (e.g. program) to see if I can keep it working properly.

This is what I have working at present:

IF(Portfolio Name="ABC",1000,IF(Portfolio Name="DEF",1100,IF(Portfolio Name="GHI",900,"")))

[Doug_Den_Hoed__AtAppStore]

Slow and steady is the ticket, Nick: you got this...<grin>

Regards,

Doug

[NickVa1]

:) Thanks, Doug.

Quick question - I can't find any documentation around the '+IF' element. Is that the official function to do a nested IF? This would allow me to create an expression on multiple fields (portfolio, program, etc.)

[Doug_Den_Hoed__AtAppStore]

Hi Nick,

Yep: this Calculated data expressions reference is definitely among my Top 10 most visited.

The pattern you seek is IF(condition A, result A, IF(condition B, result B, result C))

Regards,

Doug

[NickVa1]

Thanks, Doug.

Another question as I build this out...if you have time, or when you have time:

I've added a layer for a project's Planned Hours:

IF(Portfolio Name="ABC",1000,IF(Portfolio Name="DEF",1100,IF(Portfolio Name="GHI",900,"")))+IF(Program ID="5f90440b0008442a6f81d751fc94e1c2",1000,IF(Program ID="5f9056bb0009d0387a403a05886c4d43",1400,IF(Program ID="5f90444f00084797689d9caa1455adc6",700,"")))+IF(Planned Hours<="20",130,IF(Planned Hours>"20",275,””))

It's giving me an invalid custom expression error when trying to save. I'm wondering if I need to enter some other type of literal value instead of a number? I see Planned Hours listed as an integer field type, but when I export planned hours, it includes the word "Hours" in the exported values, which makes me think it might not be a number field, and would require a different operator (not <= or =)?

[NickVa1]

So it turns out that if you copy/paste stuff, or build the statement outside of WF and paste it in, it sometimes throws that error. I built the exact same planned hours expression, by hand direct in WF, this time and it worked. Thanks for the sounding board!

[Doug_Den_Hoed__AtAppStore]

Ah yes: the ol' copy paste ascii switcheroo.

One of my earliest experiences was a very subtle difference in the single quotes (straight up and down, vs slightly curved). I've learned to stick to either notepad or Sublime for editing text formulas.

Regards,

Doug