I am trying to investigate an issue of coming up with correct project count when my backend SQL script is a union of 5 statements.
Defining the Problem:
The data about compliance of a project comes from the first query (highlighted in yellow). To perform the UNION, I defined the Compliance Metric in the other queries as NULL. [CAST (NULL as VARCHAR (3)) AS "Compliance"]. I'm tasked to calculate the Compliance figures for 10 projects as a whole, that I'm able to successfully compute (refer to sheet 2, where 4 out of 10 projects are compliant -> hence giving me 40%).
I have a filter, by the name "Compliant Flag" that I need to fine tune in order to reflect correct calculation. Here's what I'm looking for:
If I select Compliant, give me 4 projects, and 100% as the Compliance% (TRUE-It gives me that)
If I select Non-Compliant, give me 2 projects, and 0% as the Compliance% (TRUE-It gives me that)
If I select NULL, give me 4 projects, and 0% as the Compliance% (Trying to figure out)
Now, if I select NULL, it gives me 9 projects, instead of 4 (the reason being that the 9 projects appear in one or more queries apart from the highlighted query 1). Can someone help me with this filter condition, so that it displays the correct count of projects?