Help!! Text object and Total Sum of Table got different result.

[#1]

Hi,

People of Qlik Community, I was wondering why the total on my table is not equal on the text object.

the expression i use in my table is this:

sum(aggr(DISTINCT amount, payment_trn_id,business_account_no,assessment_trn_hdr_id,tax_year))

which display 20,239,370,277.03

btw, this is the expression i put on the title of the text object i use:

=num(sum(aggr(DISTINCT amount, payment_trn_id,business_account_no,assessment_trn_hdr_id,tax_year)),  ' #,##0.## ')

and will display 20,239,424,681.39

any suggestion why it is not equal on the table?

Please see attachment guys,

P.S. payment_trn_id and assessment_trn_hfr_id is not included on attachment because it only connect the amount to business_account_no.

Thanks in advance !

[krishna20]

Hi,

Have you checked by disabling the suppress when value is null option in the dimension tab.??

[#3]

Hi,

I already did that, but i think the problem is in the text object, is there a way to avoid adding the null value on the text object?

[krishna20]

Hi,

Try this expression in text object as well as in chart.

=num(sum(aggr(DISTINCT amount,business_account_no,tax_year)),  ' #,##0.## ')

then, Go to chart properties -> Number-> Select Fixed to 2 decimals.

Check it once.

[#5]

The expression in the chart and text object are the same, but I gives me different answer.

I think I should not use the text object LOL.

[alex_millan · Accepted solution]

Hi Lester,

seems to be a problem with data associated to a null tax_year, or something like that

Note that, if you create a listbox with tax_year and select all possible values, the value in the text object matchs perfectly the total in the table chart.

If you want, you can change your expression in the text object to:

=sum({<tax_year = {*}>}aggr(DISTINCT amount, payment_trn_id,business_account_no,assessment_trn_hdr_id,tax_year))

That would fit the problem.

HTH

Regards

[settu_periasamy]

Hi,

I think the problem with the tax_year (there is null value). You can try the below expression in your Text box.

=num(sum({<tax_year={"*"}>}aggr(DISTINCT amount, payment_trn_id,business_account_no,assessment_trn_hdr_id,tax_year)),  ' #,##0.## ')

you can use the same in your table also..

[#8]

thanks

[#9]

Thank you so much sir