Calculate filtering the Data Model

Wed, 05 Aug 2015 12:30:09 GMT

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

topic Calculate filtering the Data Model in QlikView

Calculate filtering the Data Model