Specific value if 0 or blank

[Johan_Bertin]

Hello All,

I am currently trying to build a dashboard to report on the overall status of different sites. I have a site table and a task table

In the task table, I setup the following column:

• Status (Not started, In progress, Completed, etc)
• Sites (different sites from a lookup from the site table)
• Progress (slider from 0 to 100)
• Stream (look up from a stream table)

In the site table I would like to break down the progress per stream ( 1 - Assesment, etc) and display a global status (Not started, In progress, Completed).

As such I used the slider average of all the site related tasks to make my calculation:

• If Average = 0 → not started
• If Average = 100 → completed
• Else → in progress

My problem is that some sites don’t have a tasks yet and as such it reports a blank but if I use my formula logic then my report will show “In progress”

How do I make sure that blank is considered as not started?

Thank you for your help,

Johan

[Federico.Stefanato]

Hi @Johan_Bertin,
I’d slightly change the logic like this:

Tasks.Filter(Sites = thisRow && Stream = [1 - Assessment]).Progress.Average()
  .withName(progress,
    SwitchIf(
        progress = 100, "Completed",
        progress > 0 && progress < 100, "In Progress",
        "Not Started"
    )
)

(Not tried myself, just typed here: please double check for consistency errors)

This is also more readable, I guess.
Let me know if this helps.

Cheers!

[Johan_Bertin]

Hello Federico,

Thank you very much for the prompt reply. It is indeed more readable (still a bit difficult to understand the logic for now)

So I applied your formula

And now I am getting this

The line with the empty cell should display Not started since there is no data.

Any ideas?

Thanks again,

Johan

[Connor_McCormick1]

Try doing Progress.IfBlank(0).Average()

[Johan_Bertin]

It worked,

I love this community, thank you very much to the 2 of you.

Have a wonderful weekend