create quality check using a traffic light gauge

[#1]

Hello community,

I want to create a quality check using a traffic light gauge but I don't know how to write the syntax.

Each Department has to submit a report each week - so I want to display green for all departments that have submitted reports for all availabe weeks and red if one week is missing.

Fields:

CalendarWeek

Department

The Output should be a straight table which lists all Departments with a red or green traffic light next to it, depending on the result.

How do I have to construct the expression? Do I need a set analysis for it?

Thank you.

[jvitantonio · Accepted solution]

Hi, is this what you need?

[#3]

Perfect! Thank you very much.

[#4]

Out of curiosity. What if I wanted to calculate the traffic light based on two dimensions?

For example: Department_DB and Department_Excel. This is used to check whether the entry for a department in Excel is the same as the Department_DB.

Since mismatches can occur here for example "DepA" in Excel is assigned to "DepB" in the DB for a certain calendar week, how can one set the traffic light so that both dimensions are checked for the week field and result in TRUE if both equal to true?

Department_Excel	Department_DB	Week
DepA	DepA	31
DepA	DepB	32