Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Calculated Dimension Help

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

1 Reply
jsaradhi
Valued Contributor

Re: Calculated Dimension Help

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.

Community Browser