Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
e0015367
New Contributor III

Help on complicated text object formula

text object help.png

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.

1 Solution

Accepted Solutions

Re: Help on complicated text object formula

or even this:

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

3 Replies
MVP
MVP

Re: Help on complicated text object formula

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

or even this:

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

e0015367
New Contributor III

Re: Help on complicated text object formula

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