Progress Bar Dependent on Status of Tasks

[Kayla_Van]

Hi! If anyone could help me with this, that would be amazing and well appreciated.

I would like my progress bar to reflect the status changes from a subtasks table.

The subtasks table has statuses of Not Started, In Progress, Complete. I’m wondering what formula I can use. For example, if 3/6 of the subtasks are completed, I would like the progress bar be half colored in or if 1/5 of the subtasks are completed, a little bit of the progress bar to be colored in.

So far, I have used this formula Round((thisRow.Subtasks.Filter(Status=Completed).Count())/(thisRow.Subtasks.Count())*100,1) BUT it only works for when the status is complete and I can’t seem to find a way to do it for the other 3 statuses.

Thank you in advance!!

[Pch]

Hi @Kayla_Van !

The thing is that when it comes the whole cycle of a subtask in terms of statuses (going from Not Started to Completed), each status represent a portion of the progression where :

• Not Started would be 1/3 of the whole progression
• In progress would be 2/3 of the whole progression
• Complete would be 3/3 of the whole progression

So, if you want your progress bar to be precise, you also need to account for this, otherwise, if you have 2 subtasks and both are In Progress the progress bar will display 100 which is not very accurate (IMHO)

Going a little bit further with this, you could even say that the statuses :

• Not Started ranges from 0/3 <= Not Started < 1/3 of the whole progression
• In progress ranges from 1/3 <= In progress < 2/3 of the whole progression
• Complete ranges from 2/3 <= Complete < 3/3 of the whole progression

Meaning, there are 2 ways to reflect the status changes of your subtasks as the correction to apply by status can be calculated by using a minimum value where :

• Not Started is not accounted for as not needed → so, 0
  (So the progress bar display 0 if all subtasks are Not Started yet)
• In progress is 1/3 of the whole progression - 1 → so, 1/2
  (as Not Started is excluded, the whole progression cycle can be adjusted too)
• Complete is 2/3 of the whole progression - 1 → so, 2/2
  (Same reason as for In progress)

Or a maximum one where …

• Not Started is 1/3 of the whole progression but should be excluded by a Filter()
• In progress is 2/3 of the whole progression
• Complete is 3/3 of the whole progression

In the sample you’ll find below, I’ve done both because if the formulas follow the same principle for both progress bars, the correction to apply for each status changes a little bit so the results are a bit different .

But in both cases, if all the subtasks are Not Started the progress bars will return 0 and 100 if all the subtasks are Complete .

For the first Progress bar, I began to add to the Status table, a number field to find the position of each status in the table minus 1 (this is the field called Order). So the formula looks like this :

thisTable.Find(thisRow) - 1

Now, in the progress bar Progress, you’ll find this formula :

thisRow.Subtasks.Count().WithName(Subtasks_Total, 
  WithName(Status.Count() - 1,Statuses_Total, 
  Status.ForEach(
  CurrentValue.WithName(Status,
    WithName(Status.Order / Statuses_Total,Correction,
      thisRow.Subtasks.Filter(Status = Status).Count().WithName(Subtasks_per_Status,
        Round(
        ((Subtasks_per_Status * Correction)/Subtasks_Total) * 100
        ,1)
         )
        )
      )
    ).Sum()
  )
)

And what it does is :

1. thisRow.Subtasks.Count() : Counts the total of Subtasks which returns a number I store in Subtasks_Total using WithName()

2. Status.Count() - 1 counts the total of status (of rows) in my table Status which is then stored in Statuses_Total using another WithName()

3. I then take the table Status and ForEach() status where each specific status (row) is represented CurrentValue, I give the name Status using WithName()

   1. Status.Order / Statuses_Total : For each status, I calculate the correction to apply/the proportion to keep of each status when compared to the whole progression cycle (using the value in the field Order in the Status table) and this value is stored within Correction (which is a list of numbers, as we’re in for each)
   2. thisRow.Subtasks.Filter(Status = Status).Count(): For each status, I count total of subtasks per status (which also returns a list of numbers) and stores this value within Subtasks_per_Status
   3. Round( ((Subtasks_per_Status * Correction)/Subtasks_Total) * 100 ,1) : I finally calculates the rounded and corrected proportion of subtasks per status as a list of numbers (as we’re still within ForEach()) where the first one should be 0 (as the correction applied is 0, so whatever the amount of Not Started subtasks, it will be 0), the second one is the proportion of In Progress subtasks and the last one, the Complete subtasks.
   4. All that’s left, is to Sum() the values in the list of numbers

I’m sorry … It’s getting quite late here and it’s not easy to explain …

The second progress bar ([Progress 2]) in the sample follows the same pattern with few exceptions :

1. I’ve used the field [Order 2] to determine the correction to apply and I’ve used Status.Count() for the Statuses_Total (instead of Status.Count() - 1)
2. I’ve excluded the status Not Started from the whole calculation (as only the others matter here) by applying the ForEach() on Status.Filter(CurrentValue != [Not started])

I’m really sorry for the length … But I hope this helps a little

[Aurelie_PUREUR2]

Hi @Kayla_Van !

What do you mean by “do it for the 3 others” ?

[Kayla_Van]

Hi! I would like the filter to also take into account all the status. Right now, the formula I have only takes into account the “Completed” status, but I can’t get it to work for the other three statuses. Does that help? TY!

[Pch]

Hi @Kayla_Van !

You can use Contains() in your Filter() instead of = .

thisRow.Subtasks.Filter(Status.Contains([In Progress], Complete).Count()

Round(
 thisRow.Subtasks.Filter(Status. Contains([In Progress], Complete).Count()
/ thisRow.Subtasks.Count()
 * 100,
1)

This would give the total of subtasks with the status [In Progress] and Complete

Which will work as long as all your subtasks are not all [In Progress], as in this case, the progress bar will also return 100 (as [In Progress] and Complete have the same “weight” in this case)