Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

or even this:

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

View solution in original post

3 Replies
tresesco
MVP
MVP

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

or even this:

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

Anonymous
Not applicable
Author

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