Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Are you expecting 6693?
Can you share expression due to license issue
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] ))
)
give it a try with
COLUMN(8) - COLUMN(7)
Then the results is
IF(COLUMN(8) <> 0, COLUMN(8) - COLUMN(7))
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] )
Then the result is
Still the desired result is not achieved
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))