4 Replies Latest reply: Feb 2, 2015 4:26 PM by Jarrell Dunson RSS

    Help with calculated dimension

    Jarrell Dunson

      Hey everyone,

      - I want to use a calculated dimension... and show counts.  Attached is a sample...if I export to MS Excel and add a pivot table, I can see this: 

      by setting MS Excel to this:

       

      How can I do this in Qlikview?

      - I don't want to do it in a LOAD statement, because I want users to be able to select dimensions, and the chart/table calculates the values per user selections [Note:  I did not include dimensions in the sample]....

      - Do I use Aggr()?   I've tried this in an expression [per the formula, aggr(expression, dimensions) ]...but not sure, then what dimension to use?

       

      = aggr( COUNT([Gifts Id]),

        if (  max([Gift FY])=$(vFiscalYearCurrent), 'Current',

          if ( (max([Gift FY])=$(vFiscalYearCurrent)-1) and (Min([Gift FY])=$(vFiscalYearCurrent)-1), 'LYBNT-1st Time',

            if ( (max([Gift FY])=$(vFiscalYearCurrent)-1) and (Min([Gift FY])<$(vFiscalYearCurrent)-1), 'LYBNT',

            if ( (max([Gift FY])>=$(vFiscalYearCurrent)-6) AND (max([Gift FY])<=$(vFiscalYearCurrent)-2), 'SYBNT',

            if ( (max([Gift FY])<$(vFiscalYearCurrent)-6), 'Lapsed Donor', 0

        )))))

           )

       

      LYBNT = A Donor who gave (L)ast (Y)ear (B)ut (N)ot (T)his year...

      SYBNT = A Donor who gave (S)ome (Y)ear (B)ut (N)ot (T)his year...

       

      Any help would be most appreciated...

      Jarrell