Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
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

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

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

Contributor III
Contributor III

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