topic Re: Incorrect subtotals in pivot chart with conditional expression in QlikView

Incorrect subtotals in pivot chart with conditional expression

saimahasan — Tue, 12 Jun 2018 04:36:53 GMT

Hi,

Need help regarding the sub totals displayed in pivot chart. attached is the screenshot for the same:

In this i am getting the total for "Balanced Life - Individual Life" as 953520 whereas the total of that column comes to 1743630.

Expression used here is:

=Num(Round(Pick(Match(ValueList(vCashPlcmtLabel1,vCashPlcmntLabel2),'Current Year','Previous Year'),

If(Match(BO_FUND_F3_F4,'Unit Collection A/c'),If(sum({<NAV_DATE={"$(=Date(vToDate))"}>}LAST_DAY_UMR)<0,0,sum({<NAV_DATE={"$(=Date(vToDate))"}>}LAST_DAY_UMR)),Sum({<SCHNAVBD_NAV_DATE={"$(=Date(vToDate))"}>}FUND_AMOUNT)* (-1)),

If(Match(BO_FUND_F3_F4,'Unit Collection A/c'),If(sum({<NAV_DATE={"$(=Date(vPrevToDate))"}>}LAST_DAY_UMR)<0,0,sum({<NAV_DATE={"$(=Date(vPrevToDate))"}>}LAST_DAY_UMR)),Sum({<SCHNAVBD_NAV_DATE={"$(=Date(vPrevToDate))"}>}FUND_AMOUNT)* (-1)))

/ $(vDivideNum2)),'#,##0')

Re: Incorrect subtotals in pivot chart with conditional expression

qliksus — Tue, 12 Jun 2018 05:02:15 GMT

Try with aggr function

sum ( aggr(

Num(Round(Pick(Match(ValueList(vCashPlcmtLabel1,vCashPlcmntLabel2),'Current Year','Previous Year'),

If(Match(BO_FUND_F3_F4,'Unit Collection A/c'),

sum({<NAV_DATE={"$(=Date(vToDate))"}>}LAST_DAY_UMR)) * purgechar(sum({<NAV_DATE={"$(=Date(vToDate))"}>}LAST_DAY_UMR)>0 ,'-')

Sum({<SCHNAVBD_NAV_DATE={"$(=Date(vToDate))"}>}FUND_AMOUNT)* (-1)),

If(Match(BO_FUND_F3_F4,'Unit Collection A/c'),

sum({<NAV_DATE={"$(=Date(vPrevToDate))"}>}LAST_DAY_UMR) * purgechar(sum({<NAV_DATE={"$(=Date(vPrevToDate))"}>}LAST_DAY_UMR)>0,'-')

Sum({<SCHNAVBD_NAV_DATE={"$(=Date(vPrevToDate))"}>}FUND_AMOUNT)* (-1)))

/ $(vDivideNum2)),'#,##0') ,

dim1,dim2,dim3 )

Re: Incorrect subtotals in pivot chart with conditional expression

saimahasan — Tue, 12 Jun 2018 05:48:00 GMT

Not working.... Giving blank output

Re: Incorrect subtotals in pivot chart with conditional expression

saimahasan — Tue, 12 Jun 2018 05:59:26 GMT

Still not giving correct totals. Not considering "Unit Collection A/C" amount for totals.

Please suggets

Re: Incorrect subtotals in pivot chart with conditional expression

saimahasan — Tue, 12 Jun 2018 06:46:41 GMT

Thanks... but i got the solution by myself...