Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

anriretief
New Contributor III

Totals in pivot table not correct

I have the following pivot table and expression in Qlikview

BS.PNG

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

 

1 Solution

Accepted Solutions
anriretief
New Contributor III

Re: Totals in pivot table not correct

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

))))

6 Replies
Partner
Partner

Re: Totals in pivot table not correct

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
New Contributor III

Re: Totals in pivot table not correct

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

$R20IEVF.PNG

anriretief
New Contributor III

Re: Totals in pivot table not correct

I found a solution

Thank you

Partner
Partner

Re: Totals in pivot table not correct

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

Support
Support

Re: Totals in pivot table not correct

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
anriretief
New Contributor III

Re: Totals in pivot table not correct

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

))))