1 Reply Latest reply: Jun 28, 2010 4:27 AM by Martina Brenner RSS

    Creating a variance flag

      Hi All,

      I have an example pivot table where i am applying a flag to an account in a summary pivot table.

      The flag indicates whether or not the account is over 25k in variance for YTD. I want to be able to utilise a list box, whereby a user can select '25k var' and see all accounts which satisfy this criteria. The problem is, i need the formula to evaluate on an account by account basis rather than at any other level of granularity.

      Not sure whether it is a jet lag, but i can't think of a solution to this problem. any ideas?

      error loading image

      Thanks.

       

      The existing formula is as follows;

      if(
      fabs(Sum(if (InYearToDate(TranDate, vMaxDate, 0), (TranAmount * Split)))
      -
      Sum(if (InYearToDate(TranDate, vMaxDate, 0), (BudAmount * BudSplit))))
      >=25000,
      '25k var')

       

        • AW:Creating a variance flag
          Martina Brenner

          Hi, Matt,

          maybe the classfunction helps?

          class( expression, interval [ , label [ , offset ]] )

          Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

          Examples:

          class( var,10 ) with var = 23 returns '20<=x<30'

          class( var,5,'value' ) with var = 23 returns '20<= value <25'

          class( var,10,'x',5 ) with var = 23 returns '15<=x<25'