QV Expression For records with 2 condition: Max Date & Max Year

[mohdhaniff]

Hi,

Currently i got multiple records in database. How can I create expression to retrieve data with 2 specific Maximum records.

Sample Data:

Refer to QV Test.docx attached.

Note: There are 2 set of data table created

- Fixed : single record.

- Dynamic : multiple records

My Expression:

Approved:

count({$<[Application Date Year]={'>=$(=max([Application Date Year])-2)'}>}

if(index([EDL Status 2],'Appro')>0, Seq))

Note: I am using ' [Application Date Year])-2' due to my original data having records since 1977 and I wish to have records for past 2 years only.

Disbursed:

count({$<[Application Date Year]={'>=$(=max([Application Date Year])-2)'}>}

if(index([EDL Status 2],'Disb')>0, Seq))

NPL:

count({$<[Application Date Year]={'>=$(=max([Application Date Year])-2)'}>}

if(index([EDL Status 2],'Disb')>0,if(NPL='Y', Seq)))

NPL2:

Count({<[Reporting Month] = {"$(=[Reporting Month](Max({<[Application Date Year]={'$(=Max([Application Date Year])-2)'}>} [Reporting Month])))"}>} if(index([EDL Status 2],'Disb')>0,if(NPL='Y', Seq)))

My Result

My Expectation Result

For NPL 2, I am expecting to have total as below condition:

NPL 2 Condition:

a. records with past 3 years Application Date Year (2015 / 2016 / 2017);

b. from items (a), to filter records with latest Repoorting Month (Jan 2017);

c. from items (b), to filter records with NPL = Y only

[jonathandienst]

Perhaps something like:

Count({<

  [Application Date Year]={">=$(=max([Application Date Year])-2)"}>},

  [Reporting Month] = {"$(=(Max({<[Application Date Year]={$(=Max([Application Date Year])-2)}>} [Reporting Month])))"}

  >}

  if(index([EDL Status 2],'Disb')>0 And NPL='Y', Seq)

)

[mohdhaniff]

Hi Jonathan,

thanks for your advise. Unfortunately, the formula having error (maybe) after comma and no result appeared.