Totals in pivot table not correct

anriretief — Mon, 02 Sep 2019 08:26:11 GMT

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

Re: Totals in pivot table not correct

DavidM — Mon, 02 Sep 2019 10:49:03 GMT

Hi,

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

Re: Totals in pivot table not correct

anriretief — Mon, 02 Sep 2019 11:40:55 GMT

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

Re: Totals in pivot table not correct

anriretief — Mon, 02 Sep 2019 13:35:25 GMT

I found a solution

Thank you

Re: Totals in pivot table not correct

DavidM — Mon, 02 Sep 2019 14:19:37 GMT

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

Re: Totals in pivot table not correct

Brett_Bleess — Tue, 10 Sep 2019 18:43:19 GMT

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/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett

Re: Totals in pivot table not correct

anriretief — Thu, 12 Sep 2019 06:05:10 GMT

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

))))

topic Re: Totals in pivot table not correct in QlikView

Totals in pivot table not correct

Re: Totals in pivot table not correct

Re: Totals in pivot table not correct

Re: Totals in pivot table not correct

Re: Totals in pivot table not correct

Re: Totals in pivot table not correct

Re: Totals in pivot table not correct