Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

axana___
New Contributor

Qlik sense Balance Sheet

Hi all,

 

I am working on a financial application for a customer. I managed to set up a P&L and Balance sheet regarding the template I found on the forum.

Now I am stuck with the Balance sheet because of some accounts that can change from assets to liabilities and the other way around. 

If the value after applying filters of a certain account is positive, it needs to be displayed on the active side. When it is negative it needs to be displayed on the passive side. 

 I made a very simple example to illustrate what I want to become. The sum of the value needs to be displayed on VAT(A) when it is positive and when it is negative it needs to be displayed after VAT(P)

Example when VAT account value is positive

VAT(A)50
TOTAL ASSETS50
VAT(P)0
TOTAL LIABILITIES0

 

Example when VAT account value is negative

VAT(A)0
TOTAL ASSETS0
VAT(P)50
TOTAL LIABILITIES50
1 Solution

Accepted Solutions

Re: Qlik sense Balance Sheet

Try this

Sum(
Aggr(
If(Count(TOTAL <Rekening> DISTINCT FlagAPChangeSection) = 1, Sum(Saldo),
If(Sum(TOTAL <Rekening> Saldo) > 0, Sum({<FlagAPChangeSection = {'A'}>} Saldo), Sum({<FlagAPChangeSection = {'P'}>} Saldo)))
, Rekening, FlagAPChangeSection, [Balans Heading Management])
)
14 Replies
Partner
Partner

Re: Qlik sense Balance Sheet

Need more details to help properly but did you try if condition?
i.e. in Assets if(YOUR_EXPERSSION>0,YOUR_EXPERSSION,0)
and other way round is liability
axana___
New Contributor

Re: Qlik sense Balance Sheet

Yes I had that solution too but since there are 4 accounts that can change it is becoming a very big if statement. Then the subtotals are also not correct anymore so I needed to adjust them as well with the if statement. 

Partner
Partner

Re: Qlik sense Balance Sheet

Can you give a sample app? i can help then
axana___
New Contributor

Re: Qlik sense Balance Sheet

Thanks for wanting to help me! I made a sample app to work with.

Some explanation:

when you select 02/03/2018 the data is displayed at  BTW(A) and Vennootschapsbelasting (A) because I made the template to standardly display on active side. But when you select 02/04/2018 the values of BTW(A) and Vennootschapsbelasting(A) become negative. At this moment these values should move to BTW(P) and Vennootschapsbelasting(P).

Re: Qlik sense Balance Sheet


@axana___ wrote:

when you select 02/03/2018 the data is displayed at  BTW(A) and Vennootschapsbelasting (A) because I made the template to standardly display on active side. 


Exactly the opposite of you mentioned is happening right now. When I select 02/03/2018... I am seeing BTW(A) and Vennootschapsbelasting (A) to be 0, but Ps have value

image.png


@axana___ wrote:

But when you select 02/04/2018 the values of BTW(A) and Vennootschapsbelasting(A) become negative. 


Now when I select 02/04/2018, BTW(A) and Vennootschapsbelasting(A) are positive, but Ps are negative

image.png

Would you be able to provide the exact numbers you expect to see when you select 02/03 and 02/04?

axana___
New Contributor

Re: Qlik sense Balance Sheet

This is what you should see when 02/03/2018 is selected:

BTW (A)5
Cash10
Vennootschapsbelasting (A)15
Totaal activa 
BTW (P)0
rsz18
Vennootschapsbelasting (P)0

 

This is what you should see when 02/04/2018 is selected:

BTW (A)0
Cash2
Vennootschapsbelasting (A)0
Totaal activa 
BTW (P)-4
rsz18
Vennootschapsbelasting (P)-7

Re: Qlik sense Balance Sheet

Not 100% sure, but try this

Aggr(
If(Count(TOTAL <Rekening> DISTINCT FlagAPChangeSection) = 1, Sum(Saldo),
If(Sum(TOTAL <Rekening> Saldo) > 0, Sum({<FlagAPChangeSection = {'A'}>} Saldo), Sum({<FlagAPChangeSection = {'P'}>} Saldo)))
, Rekening, FlagAPChangeSection, [Balans Heading Management])
axana___
New Contributor

Re: Qlik sense Balance Sheet

Yes thanks, your .qvf file is working! Except for the subtotals. Those are empty instead of a calculation of the above. Do you have a solution for this as well? 

axana___
New Contributor

Re: Qlik sense Balance Sheet

I have another question about a problem that occured. With the first dummy application, every heading was linked with 1 account number. In the real data it is possible that a heading is a combination of multiple account numbers. When I implement your solution I get a result with every heading that has 1 account number but the headings who are a combination of multiple account numbers are not displayed. 

I added for BTW (A) a second account number to illustrate the problem. You can see this on the screenshot below and I also attached a new sample file.

Example: when BTW (A) only has account number 61000, the data is shown properly. When I insert another BTW (A) row in the Accountmaster template with a differen account number for example 61100, I get to see "-" in the table.

Problem account numbers.PNG