Best Of

You just want a filter for the view, right? You can have multiple conditions in a filtered view.

Filtering for Tstart<=X AND Tend>=X seems like it should do the job for you for any value of X, they would just need to change the value of X for the week they want.

Hello, I was able to get both formulas to work after testing out the =SUM([Completed Count]:[Completed Count]). Thank you for your input.

@Jesse G. It did work! Thank you.

Hey @aneekahTAL

The syntax for your COUNTIFS is not correct. COUNTIFS have the syntax of =COUNTIFS({Range 1}, criteria 1, {Range 2}, criteria, {Range 3 etc}, criteria 3}. A COUNTIFS can also be used with only one Range-criteria pair.

=COUNTIFS({TPP Rollout - Hubs Range 2}; "A"; {State 9}; "Complete"; {TPP Rollout - Hubs Range 1}; "FY25")

Does this work for you?
Kelly

@Georgie ,

I figured it out!

I used this formula

=IFERROR(AVG(CHILDREN()),

IF(Status@row = "Complete", "1",

IF(Status@row = "In Progress", ".25",

IF(Status@row = "Blocked", ".5",

IF(Status@row = "Not Started", "0",

"")))))

Then I formatted the column with Percentage

Hi @Paul.Woodward

The third IF condition ([Month of Contract]@row > [Contract Length]@row + 12) is never reached because the second IF already catches all cases where [Month of Contract]@row > [Contract Length]@row.

For example:
If the Contract Length is 12 months and the Month of Contract is 15, it meets the condition [Month of Contract]@row > [Contract Length]@row but does not meet [Month of Contract]@row > [Contract Length]@row + 12. The formula should apply the "After Initial Contract %" rate in this case.

However, if the Month of Contract is 25, it does meet both conditions ([Month of Contract]@row > [Contract Length]@row and [Month of Contract]@row > [Contract Length]@row + 12). The formula should apply the "12+ Month Contract %" rate in this case.

So, bring the IF([Month of Contract]@row > [Contract Length]@row + 12, before, and [Month of Contract]@row > [Contract Length]@row will be treated as the "else" case if not.

=IF([Month of Contract]@row <= [Contract Length]@row, 
    [Contract Amount]@row * [Initial Contract %]@row, 
    IF([Month of Contract]@row > [Contract Length]@row + 12, 
        [Contract Amount]@row * [12+ Month Contract %]@row, 
        [Contract Amount]@row * [After Initial Contract %]@row
    )
)

In the Corrected Formula:

• If [Month of Contract]@row <= [Contract Length]@row → Apply Initial Contract %.
• Else, if [Month of Contract]@row > [Contract Length]@row + 12 → Apply 12+ Month Contract %.
  • This condition will be reached if the contract month is more than 12 months past the contract length.
• Else → Apply After Initial Contract %.
  • This part catches all cases greater than [Contract Length]@row but less than [Contract Length]@row + 12.