Convert chart formula in order to be able to display total in text box

[mellerbeck]

For an accounting straight table I have

if(sum({1}total <BillTo> [First Exclusion]) / sum({1}total <BillTo> [TotalDue])>0.2 AND BBC='-' AND [Residual Short Pays]=0 ,sum(CurrentDue + DaysPast0To30 + DaysPast31To60),0)

Basically if the sum of all the [First Exclusion] divided by the sum of all of the TotalDue is greater than .20 then pass through the sum of currentDue + DaysPast0To30 + DaysPast31To60

I tried

sum(if(sum({1}total <BillTo> [First Exclusion]) / sum({1}total <BillTo> [TotalDue])>0.2 AND BBC='-' AND [Residual Short Pays]=0 ,CurrentDue + DaysPast0To30 + DaysPast31To60,0))

And I messed with aggr, but no luck

Any thoughts and pointers much appreciated!

-Michael

[mellerbeck]

Ahh I got it

=sum(aggr( if( sum( total <BillTo> [First Exclusion])/ sum( total <BillTo> TotalDue) >.2 AND BBC='-' AND [Residual Short Pays]=0, sum( CurrentDue + DaysPast0To30 + DaysPast31To60),0),BillTo))

But now I'm trying to make it so that it will ignore selections. It's resisting my efforts

[johnw]

You may need the {1} in more places than you expect.  You'd need sum({1} aggr(sum({1}... for instance.  Basically, stick your set expression on every aggregation function.  Mind you, you might already be doing that.  It's just a common error I see, and sometimes still make.

[mellerbeck]

Have you ever seen where it doesn't work even if you use {1} most every where

=sum({1} aggr(if( sum({1} total <BillTo> [First Exclusion])/ sum({1} total <BillTo> TotalDue) >.2 AND BBC='-' AND [Residual Short Pays]=0, sum({1} CurrentDue + DaysPast0To30 + DaysPast31To60),0),BillTo))

It looks like it might need to be after the aggr as well? but the syntax highlighter doesn't seem to care for it and it still doesn't ignore selections. oh well not critical, just trying to figure it out