I have a field called LOANDATASOURCE that specifies whether loan data comes from one of 3 databases.
One loan data source (FICS) houses all loans regardless of whether we consider them open or not. We determine an open loan by Investor Bank Code, Investor Code and Balance > 0. The other 2 sources only have open loans. We also have a map that corresponds the loan type to specific categories; FICS is always type 500 = 1st Mortgage Category, the other data sources also have types of First Mortgage.
What I am trying to do is to use a calculated dimension for [LOAN CATEGORY] that only counts FICS loans if Investor Bank Code = 001 and Investor Code = 001 and Balance > 0 so that my 1st mortgage balances in the chart are accurate. Does anybody have an idea of how to accomplish this?
I've tried this:
=if(LOANDATASOURCE = 'FICS' and FICSINVBANKCD = 001 and FICSINVCD = 001 and BALANCE > 0, 'FICS 1st Mtg',