Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
upaliwije
Contributor II

Total Amount Incorrect

I have in qv Document the following pivot table.

Screenshot_1.png

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

1 Solution

Accepted Solutions

Re: Total Amount Incorrect

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

Capture.PNG

View solution in original post

13 Replies

Re: Total Amount Incorrect

Are you expecting 6693?

Can you share expression due to license issue

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
upaliwije
Contributor II

Re: Total Amount Incorrect

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

)

prieper
Honored Contributor II

Re: Total Amount Incorrect

give it a try with

COLUMN(8) - COLUMN(7)

upaliwije
Contributor II

Re: Total Amount Incorrect

Then the results is Screenshot_2.png

prieper
Honored Contributor II

Re: Total Amount Incorrect

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

Re: Total Amount Incorrect

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

upaliwije
Contributor II

Re: Total Amount Incorrect

Then the result is

Screenshot_1.png

upaliwije
Contributor II

Re: Total Amount Incorrect

Still the desired result is not achieved

Screenshot_1.png

Re: Total Amount Incorrect

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

Capture.PNG

View solution in original post