Qlik sense Balance Sheet

[axana___]

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 ASSETS	50
VAT(P)	0
TOTAL LIABILITIES	0

Example when VAT account value is negative

VAT(A)	0
TOTAL ASSETS	0
VAT(P)	50
TOTAL LIABILITIES	50

[dplr-rn]

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___]

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. 

[dplr-rn]

Can you give a sample app? i can help then

[axana___]

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

[sunny_talwar]

---

@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

---

@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

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

[axana___]

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

BTW (A)	5
Cash	10
Vennootschapsbelasting (A)	15
Totaal activa
BTW (P)	0
rsz	18
Vennootschapsbelasting (P)	0

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

BTW (A)	0
Cash	2
Vennootschapsbelasting (A)	0
Totaal activa
BTW (P)	-4
rsz	18
Vennootschapsbelasting (P)	-7

[sunny_talwar]

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___]

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___]

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.