Comparing Columns to Identify Health

ltomasello

Hello,

I'm really struggling here and I'm sure I can do something simple but I'm just not getting it.

Here's my task: Identify if any of our projects, which happen to overlap sometimes, are installing same version of software. If they are not, I need to flag that. I'm currently using "Green" and "Red" traffic lights for that.

In the image below. I want to check that "Version Installed per Deployment" is equal to " OEMV Credit Package Version" equal to "Chase FF Credit Package Version", equal to "vStore Credit Package Version". If they all are (and ignore blanks), then we get a Green Light. If there is a mismatch like the second row, then we should have Red Light.

I've worked through different formulas in every different way as well as adding other columns to help me identify as a number to be able to subtract (trying to think outside the box).

I'd appreciate the help.

Gia Thinh

Hi @ltomasello,

The idea is to count the distinct of versions in a row (ignore blanks).

If count = 0 then flag is "blank"

If count = 1 then flag is "green"

Else flag is "Red"

Try this formula in the Flag column:

=IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 0, "", IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 1, "Green", "Red"))

Hope that helps.

Gia Thinh

Hi @ltomasello,

The idea is to count the distinct of versions in a row (ignore blanks).

If count = 0 then flag is "blank"

If count = 1 then flag is "green"

Else flag is "Red"

Try this formula in the Flag column:

=IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 0, "", IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 1, "Green", "Red"))

Hope that helps.

ltomasello

Hi Gia,

Thank you so much. This totally did it. I would have not been able to come up with this. I've learned something new today.

Thank you🤗

Gia Thinh

I am happy to help.