Boolean Result from Aggr Function

[#1]

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.

[swuehl · Accepted solution]

Try

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

[#3]

See, thought it was a simple one, I'm at the end of a silly long day.

Thanks very much for the reply, appreciate it.