topic Re: Total Amount Incorrect in QlikView

Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 09:20:50 GMT

I have in qv Document the following pivot table.

In the Difference the total shown is incorrect. It may be probably due to incorrect expression. pls look into this in my attached QV document and advise me to correct it

Re: Total Amount Incorrect

Anil_Babu_Samineni — Wed, 05 Jul 2017 09:23:27 GMT

Are you expecting 6693?

Can you share expression due to license issue

Re: Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 09:26:48 GMT

Yes 6693

My expression

=if(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )>0,

(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )

sum( {$<[R_YEAR] = {'$(=max([R_YEAR]))'}>} [PREMIUM] ))

)

Re: Total Amount Incorrect

prieper — Wed, 05 Jul 2017 09:27:41 GMT

give it a try with

COLUMN(8) - COLUMN(7)

Re: Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 09:36:55 GMT

Then the results is

Re: Total Amount Incorrect

prieper — Wed, 05 Jul 2017 09:40:05 GMT

IF(COLUMN(8) <> 0, COLUMN(8) - COLUMN(7))

Re: Total Amount Incorrect

Kushal_Chawda — Wed, 05 Jul 2017 09:45:09 GMT

there is some syntax problem in your expression

try below

=if(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )>0,

(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )),0)

sum( {$<[R_YEAR] = {'$(=max([R_YEAR]))'}>} [PREMIUM] )

Re: Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 09:46:36 GMT

Then the result is

Re: Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 09:50:56 GMT

Still the desired result is not achieved

Re: Total Amount Incorrect

sunny_talwar — Wed, 05 Jul 2017 11:52:34 GMT

May be this

=Sum(Aggr(if(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )>0,

(sum( {<R_YEAR=,R_MONTH=, [To_MONTH] = {$(=concat([R_MONTH],','))},[To_YEAR] = {$(=max([To_YEAR]+1))},POL_TYPE={'R'}>} [PREMIUM] )

sum( {$<[R_YEAR] = {'$(=max([R_YEAR]))'}>} [PREMIUM] ))

), POLICY_NO))

Re: Total Amount Incorrect

Kushal_Chawda — Wed, 05 Jul 2017 12:05:47 GMT

what is expected output?

Re: Total Amount Incorrect

upaliwije — Wed, 05 Jul 2017 16:25:22 GMT

Thank you very much Sir, You have always helped me with valuable guidance. Still I am unable understand how the Aggr function works. Kindly elaborate it to me or provide me with information of blogs/books on the subject

Thanks

Re: Total Amount Incorrect

sunny_talwar — Wed, 05 Jul 2017 16:27:14 GMT

Without the Sum(Aggr(....)) the total rows is also evaluated for the If condition, but by using Sum(Aggr(....)), you can telling your chart to show the sum of rows as the total instead of using the if condition for the total row.

Does that make sense?

Re: Total Amount Incorrect

upaliwije — Thu, 06 Jul 2017 15:23:40 GMT

Yes Thanks a lot