Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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 :
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