Blank in Set Analysis

[guillaume_gorli]

Hi Guys,

Here is a tricky one.

I have two database.

First one is listing hours produced each days on different works orders

Variables are :

• Date
• Work Order
• Actual Hours

Second one is listing the date those work orders have been completed, the status and the standard hours estimated for each work orders

Variables are :

• Date_Closed which is the date when the work order has been declared closed in the system. Please note it could be empty if the work order is yet to be closed.
• Work Order
• Status (closed or open)
• Standard Hours

I need to produced different KPI on the same application and the same sheet, thus i created two calendars

Calendar1 is based on variable Date and Calendar2 is based on variable Date_Closed

KPI1 is actual hours produced per month / per year : it is based on calendar1

KPI2 is actual hours produced on closed work orders  vs standard hours per month (of closing time) / per year (of closing time) : it is based on calendar2. Meaning if 10 hours have been produced in January and 5h in February on work orders A which has been closed on Frebruary, then 15h will be counted in February. i am here measuring productivity per period

To ease the use of the application, i am using set analysis so that selection on calendar1 is not polluting results on KPI2 and selection of calendar2 is not polluting results of KPI1

KPI2 is ok : if i select « 2020 » as closing year, i managed to have hours produced against work orders closed in 2020 including hours produced in 2019 AND 2020 using :

Sum({$ <Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC},Année={2019,2020}>}[Actual Hours])

KPI1 is not working as basically, what i am trying to write down is the results of all hours produced in 2020 even if date_closed is empty (meaning the work order is not closed).

I am not sure i am clear enough but to sum up :

Hours produced in the total database are 30h. This is shown in KPI1. i want here the hours not matter is the work order is closed or not.

If a year of work order closing date is selected, it should remain 30h.

If i select 2020, the results turn to 12 as 18h have been produced against not closed work orders.

Basically, i need to include in set analysis the blank or null criteria.

I have tried different things : using Blank or NULL is set analysis, define Null in the script, etc... but nothing appears to be working.

If some one is willing to help, i attach both test data base and .qvw

Thanks in advance

Guillaume