2 Replies Latest reply: Feb 13, 2013 12:26 PM by Nigel West RSS

    Boolean Result from Aggr Function

      Hi Folks

       

      I have this expression:

       

      ConCat

       

      (
      DISTINCT
      IF(FABS(Aggr(Sum(YTD_DIFF_REVS)/Sum(YTD_CMI_REVS),YTD_TRADE_DATE))>0.005,
      Date(YTD_TRADE_DATE,'DD/MM/YYYY')&
      '; CMI Revs : ' &
      Num(Aggr(Sum(YTD_CMI_REVS),YTD_TRADE_DATE),'#,##0.00')&
      '; QV Revs : ' &
      Num(Aggr(Sum(YTD_BO_REVS),YTD_TRADE_DATE),'#,##0.00')&
      '; DIFF $ : ' &
      Num(Aggr(Sum(YTD_DIFF_REVS),YTD_TRADE_DATE),'#,##0.00')&
      '; DIFF % : ' &
      Num(Aggr(Sum(YTD_DIFF_REVS)/Sum(YTD_CMI_REVS),YTD_TRADE_DATE),'#,##0.00%')&
      chr

      (13)
      )

       

      This produces a lovely list of items that are outside of a certain threshold, puts them into a big concatenated string so I can potentially use this in an email alert.

       

      However (there always seems to be one of those).....

       

      In order to trigger this alert I need to know if any one of the calculations for any date produces a result greater than 0.005, so I have simply created a statement on an email alert as follows:

       

      =FABS(Aggr(Sum(YTD_DIFF_TRADES)/Sum(YTD_CMI_TRADES),YTD_TRADE_DATE))>0.005

       

      Which of course I thought would work.

       

      But it doesn't, and if I put that formula into a simple text box it gives me a NULL response. There must be something very silly that I'm missing here, but I can't figure out what that is.

       

      Thanks for any help.

       

      Nigel.