function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AJFAJF 

Assign multiple scores a to pick list values and then total scores

I am trying to find a way to total scores assigned to multi-select picklist values that have a ranking. 

 

I have a field that is a multi-select pick list called Project Type and each Project Type value has a complexity level and two scores - Base Turnaround Time (TAT) and Additional TAT (E.g.. New Plan:  Complexity = 1; Base TAT = 10; Additional TAT=4). Total TAT is calculated by finding the Base TAT, which is the Base TAT of the most complex task, plus the additional TAT of all other tasks selected in the multi select picklist. I can find the Base TAT using a formula field (included and multi-level if off the ) but I can't find a way to include the additional value scores then total them together.

 

Any advice on how to get this would be greatly appreciated.

 

Thanks.

Ispita_NavatarIspita_Navatar

Do you wish to implement it using formulas only?

If yes then let me try it:-

So u say u have a formula field which holds the Max Base TAT-

say Field1=if(CONTAINS(text, compare_text1) ,x,if(CONTAINS(text, compare_text2) ,y,if(CONTAINS(text, compare_text3),z,0))) 

  {take care that the highest one is the first if , then the next highest and so on so that you get the highest TAT only}

 

Now for the Additional TAT:-

Say Field2 = if(CONTAINS(text, compare_text1) ,x,0) + if(CONTAINS(text, compare_text2) ,y,0) + if(CONTAINS(text, compare_text3),z,0)

 

Total TAT= Field1 + Field2

                = if(CONTAINS(text, compare_text1) ,x,if(CONTAINS(text, compare_text2) ,y,if(CONTAINS(text, compare_text3),z,0)))  + if(CONTAINS(text, compare_text1) ,x,0) + if(CONTAINS(text, compare_text2) ,y,0) + if(CONTAINS(text, compare_text3),z,0)

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

 

 

 

 

 
AJFAJF

Ispita_Navatar, thanks for the reply. To confirm, I am open to other solutions, not just using formulas. I always like to start with trying simple solutions, but know that this is not always possible.

 

The challenge with the solution you offered is that it doesn't take into account the value of Field 1 when calculating Field 2. For example, here is a list of tasks with Base TAT and Additional TAT:

Task  Base TAT  Additional TAT

  1          20                   4

  2          20                   4

  3          10                   3

  4          10                   2

  5          5                     1

 

To calculate the Total TAT (Base + Additional), I need the Base TAT of the most complex task (1 being most complex) for a project and then I need to add any Additional TAT for included tasks, given the most complex task (Base TAT) has already been set. So if a project involves tasks 1, 3 and 5, then TAT = 20 (Base TAT) + 3 (Addnl TAT) + 1 (Addnl TAT) = 24 days. Alternatively, if a project involves tasks 1 and 2, then TAT would be 20 (Base TAT) + 4 (Additional TAT).

 

I hope that helps clarify what I am trying to achieve and I appreciate the help.

 

Regards

 

Anthony