topic pivot table total shows zero in QlikView

pivot table total shows zero

agni_gold — Tue, 08 Nov 2016 16:29:26 GMT

Hi Friends,

I have one pivot table in which 5 dimensions and i am showing total , but total is coming zero.

Any idea guys .

Thanks

Re: pivot table total shows zero

sunny_talwar — Tue, 08 Nov 2016 16:57:25 GMT

What is your expression? Is it an if statement? May be you need to use Sum(Aggr()) around your expression

Re: pivot table total shows zero

agni_gold — Tue, 08 Nov 2016 17:00:46 GMT

here is the expression

(if(Dimensionality()>=3 and (Only(Priorities)='Elevate the Core' or GetFieldSelections(Priorities)='Elevate the Core'),

((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vETC_LY_AC_VPA41_ZB1)/1000000),

if(Dimensionality()>=3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),

((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vNAB_LY_AC_VPA59_ZB1)/1000000),

if(Dimensionality()>=1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),

sum({$<_PFlag={'EXT'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000,

sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000

))))

Re: pivot table total shows zero

sunny_talwar — Tue, 08 Nov 2016 17:04:12 GMT

Not sure what your dimensions are, but try like this (assuming you have two dimensions; Dim1 and Dim2)

Sum(Aggr(

(if(Dimensionality()>=3 and (Only(Priorities)='Elevate the Core' or GetFieldSelections(Priorities)='Elevate the Core'),

((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vETC_LY_AC_VPA41_ZB1)/1000000),

if(Dimensionality()>=3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),

((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vNAB_LY_AC_VPA59_ZB1)/1000000),

if(Dimensionality()>=1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),

sum({$<_PFlag={'EXT'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000,

sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000

))))

, Dim1, Dim2))

Re: pivot table total shows zero

agni_gold — Tue, 08 Nov 2016 17:16:46 GMT

I have 5 dimensions , but on 3rd dimension i need to do some other calculation for particular value , rest should work as its .

so should i add all 5 dimension in your above expression?

Re: pivot table total shows zero

sunny_talwar — Tue, 08 Nov 2016 23:02:36 GMT

Not entirely sure you are doing, but may be this:

If(Dimensionality() >= 3 and (Only(Priorities) = 'Elevate the Core' or GetFieldSelections(Priorities) = 'Elevate the Core'),

Sum({$<_PFlag = {'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000 + $(vETC_LY_AC_VPA41_ZB1)/1000000,

If(Dimensionality() = 3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),

Sum({$<_PFlag = {'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000 + $(vNAB_LY_AC_VPA59_ZB1)/1000000,

If(Dimensionality() >= 1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),

Sum({$<_PFlag = {'EXT'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000,

Avg(Aggr(Sum({$<_PFlag={'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK={'AC'}>} VOL_PREV_AMENDED)/1000000, Dim1, Dim2, Dim3, Dim4, Dim5))

)))