topic Re: Help on complicated text object formula in QlikView

Help on complicated text object formula

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Help on complicated text object formula

tresB — Tue, 22 Nov 2016 10:05:10 GMT

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))

Re: Help on complicated text object formula

sunny_talwar — Tue, 22 Nov 2016 10:31:18 GMT

or even this:

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

Re: Help on complicated text object formula

Anonymous — Wed, 23 Nov 2016 01:25:13 GMT

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