Help on complicated text object formula

[Anonymous]

With reference to the screenshot, i want to create a text object that show the total of the last column, sum(fin_gl_line_reporting_amt)*if(sum(fin_gl_line_reporting_amt)>0,1,0)  .

I can do this on a chart but not in a textbox.

I believe i need to make use of aggr function but i have no idea how to write the correct one.

Currently my formula is

=num(sum(aggr(if(sum(fin_gl_line_reporting_amt)>0,Sum(fin_gl_line_reporting_amt),fin_accnt_code)),'$#,##0.00;$#,##0.00')

but its not displaying anything, i wonder whats wrong. I have attached my qvw and excel data too.

Any help to simplify the whole formula or change it entirely is greatly appreciated.

[tresesco]

Try :

=Sum(Aggr(sum(fin_gl_line_reporting_amt)*if(sum(fin_gl_line_reporting_amt)>0,1,0), fin_accnt_code,fin_gl_line_trans_datetime))

[sunny_talwar · Accepted solution]

or even this:

=Sum(Aggr(RangeMax(Sum(fin_gl_line_reporting_amt), 0), fin_accnt_code, fin_gl_line_trans_datetime))

[Anonymous]

thank guys, looks like both of the formulas are working. the usage of range max with 0 and tablefield as the parameter is smart!