I want to filter the data model with the following criteria in order to calculate how many reports are pending to be reported in each month:
IDs with a Test End Date < Year Month
IDs with a ReportApprovalDate > Year Month + ReportApproval Date = Null
See below an example:
ID1; Test End Date = 1-Jan-14; Report Approval Date = NullI
D2; Test End Date = 1-Jan-14; Report Approval Date = 3-Jan-14
ID3; Test End Date = 1-Jan-14; Report Approval Date = 27-Feb-14
ID4; Test End Date = 1-Jan-14; Report Approval Date = 17-Apr-14
In the Plot we should have the Year-Months in the X axe and the count of Pending Reports in the Y axe. In this case, see below the result that we should get:
Year Month
Count Pending Reports
IDs that should be counted
2014-January
3
ID1, ID3 and ID4
2014-February
2
ID1 and ID4
2014-March
2
ID1 and ID4
2014-April
1
ID1
How is the best way to do this? I have tried several ways without success. The only way that I think that could be valid is creating an auxiliary data table inside the script (see the “AuxiliarTable” sheet in the attached excel table).
Is there a way to calculate the pending reports adding an dinamic expression in the chart?
