Hi,
I am trying to do the following, let me explain:
In my data model I have:
- ID
- Test End Date
- Report Approval Date
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?
Thanks in advance
Sara
