Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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',
if(LOANDATASOURCE = 'SYMITAR' and LOANTYPEDESCRIPTION <>'FICS Mortgage', [LOAN CATEGORY CALL REPORT],
[LOAN CATEGORY CALL REPORT]))
and this:
=IF({<FICSINVBANKCD = {001}, FICSINVCD = {001}>}[LOAN CATEGORY CALL REPORT] = 'FICS Mtg.', [LOAN CATEGORY CALL REPORT])
if it is supposed to be a dimension in a chart, then i think you need to aggregate it...something like
=aggr(/*your if statement*/, [LOAN CATEGORY CALL REPORT])
may be you have already ruled against it but i would look into creating a new field in script based on the conditions and use that field as dimension.