1 Reply Latest reply: Apr 23, 2014 4:47 PM by Saradhi Balla RSS

    Calculated Dimension Help

    Breanna Hume

      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])

        • Re: Calculated Dimension Help
          Saradhi Balla

          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.