Totals in pivot table not correct

[anriretief]

I have the following pivot table and expression in Qlikview

if(BS_Level4='Trade and other Receivables',sum({<Type={'204'},Statement={'BS'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'}>}YTD_Balance)>0,
sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'}>}YTD_Balance),0),

if(BS_Level4='Current Tax Receivables',if(sum({<Type={'405'},Statement={'BS'}>}YTD_Balance)>0,sum({<Type={'405'},Statement={'BS'}>}YTD_Balance),0),

if(BS_Level4='Cash and Cash Equivalents',if(sum({<Type={'206'},Statement={'BS'}>}YTD_Balance)>0,sum({<Type={'206'},Statement={'BS'}>}YTD_Balance),0),

if(BS_Level4='Trade and other Payables',-sum({<Type={'404'},Statement={'BS'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'}>}YTD_Balance)>0,
-sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'}>}YTD_Balance),0),

if(BS_Level4='Overdraft',if(sum({<Type={'206'},Statement={'BS'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'}>}YTD_Balance),0),

if(BS_Level4='Current Tax Payable',if(sum({<Type={'206'},Statement={'BS'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'}>}YTD_Balance),0),

fabs(sum({<Statement={'BS'}>}YTD_Balance))

))))))

As can be seen the totals of the pivot table are not correct

I am thinking that aggregation should be used but I am not sure how to apply it in this particular instance

Any help would be greatly appreciated

Thank you in advance

[DavidM]

Hi,

your totals in pivot table are correct - they are the sum of fields above. Not sure what are you trying to achieve here

[anriretief]

For example if you were to look at current assets the total should be 107 173 625.1 and not 104 743 751.46

[anriretief]

I found a solution

Thank you

[DavidM]

Just out of curiosity - was it one of the IF statements?

[Brett_Bleess]

Anri, it would be great if you could provide further details on what the issue was, so others can learn from it.  You can post that and once posted, you can then use the Accept as Solution button to mark it as the solution.

I am going to leave the following Help link though, as I figure this may have been related:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett

[anriretief · Accepted solution]

I simply used if statements to do the different calculations for each dimensionality(). My formula read as follows:

if(dimensionality()>=4,

if(BS_Level4='Trade and other Receivables',sum({<Type={'204'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level4='Current Tax Receivables',if(sum({<Type={'405'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'405'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level4='Cash and Cash Equivalents',if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level4='Trade and other Payables',-sum({<Type={'404'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
-sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level4='Overdraft',if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level4='Current Tax Payable',if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0),

if(BS_Level1='Total Equity and Liabilities' ,-sum({<Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),sum({<Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance))

)))))),

if(dimensionality() =3,

if(BS_Level3='Non-Current Assets',sum({<Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(BS_Level3='Current Assets', sum({<Type={'204'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0) +

if(sum({<Type={'405'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'405'},Statement={'BS'},BS_Level4={'Current Tax Receivables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'206'},Statement={'BS'},BS_Level4={'Cash and Cash Equivalents'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

sum({<Statement={'BS'},BS_Level4-={'Trade and other Receivables','Current Tax Receivables','Cash and Cash Equivalents'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(match(BS_Level3,'Non-Current Liabilities','Equity'), -sum({<Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(BS_Level3='Current Liabilities', -sum({<Type={'404'},Statement={'BS'},BS_Level4={'Trade and other Payables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
-sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Payables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Overdraft'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Current Tax Payable'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

-sum({<Statement={'BS'},BS_Level4-={'Current Tax Payable','Overdraft','Trade and other Payables'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)


)))),


if(dimensionality()=2,

if(BS_Level2='Assets', sum({<Statement={'BS'},BS_Level3={'Non-Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+

sum({<Type={'204'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},BS_Level3={'Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},BS_Level3={'Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0) +

if(sum({<Type={'405'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'405'},Statement={'BS'},BS_Level4={'Current Tax Receivables'},BS_Level3={'Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'206'},Statement={'BS'},BS_Level4={'Cash and Cash Equivalents'},BS_Level3={'Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

sum({<Statement={'BS'},BS_Level4-={'Trade and other Receivables','Current Tax Receivables','Cash and Cash Equivalents'},BS_Level3={'Current Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(BS_Level2='Equity',-sum({<Statement={'BS'},BS_Level3={'Equity'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(BS_Level2='Liabilities',-sum({<Statement={'BS'},BS_Level3={'Non-Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+

-sum({<Type={'404'},Statement={'BS'},BS_Level4={'Trade and other Payables'},BS_Level3={'Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
-sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Payables'},BS_Level3={'Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Overdraft'},BS_Level3={'Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Current Tax Payable'},BS_Level3={'Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

-sum({<Statement={'BS'},BS_Level4-={'Current Tax Payable','Overdraft','Trade and other Payables'},BS_Level3={'Current Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)

))),


if(dimensionality()=1,

if(BS_Level1='Total Assets', sum({<Statement={'BS'},BS_Level3={'Non-Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+

sum({<Type={'204'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},BS_Level3={'Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Receivables'},BS_Level3={'Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0) +

if(sum({<Type={'405'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'405'},Statement={'BS'},BS_Level4={'Current Tax Receivables'},BS_Level3={'Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,sum({<Type={'206'},Statement={'BS'},BS_Level4={'Cash and Cash Equivalents'},BS_Level3={'Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

sum({<Statement={'BS'},BS_Level4-={'Trade and other Receivables','Current Tax Receivables','Cash and Cash Equivalents'},BS_Level3={'Current Assets'},BS_Level2={'Assets'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),

if(BS_Level1='Total Equity and Liabilities', -sum({<Statement={'BS'},BS_Level3={'Equity'},BS_Level2={'Equity'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+

-sum({<Statement={'BS'},BS_Level3={'Non-Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+

-sum({<Type={'404'},Statement={'BS'},BS_Level4={'Trade and other Payables'},BS_Level3={'Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)+
if(sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)>0,
-sum({<GL_Code={'04-970-00','04-970-01','04-970-02','04-970-03','04-971-00','04-980-00','04-970-10','04-970-20'},Statement={'BS'},BS_Level4={'Trade and other Payables'},BS_Level3={'Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Overdraft'},BS_Level3={'Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

if(sum({<Type={'206'},Statement={'BS'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)<0,-sum({<Type={'206'},Statement={'BS'},BS_Level4={'Current Tax Payable'},BS_Level3={'Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance),0)+

-sum({<Statement={'BS'},BS_Level4-={'Current Tax Payable','Overdraft','Trade and other Payables'},BS_Level3={'Current Liabilities'},BS_Level2={'Liabilities'},[FIN JAAR KORT]={'$(=MAX([FIN JAAR KORT]))'}>}YTD_Balance)
))

))))