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

[sunny_talwar]

What output do you expect to see now? Would you be able to give me the numerical values when you select 02/03/2018 and numerical values when you select 02/04/2018

[axana___]

Final result for 02/03/2018:

- BTW (A) is now a sum of account number 61000 and 61100

BTW (A)	6
Cash	10
Vennootschapsbelasting (A)	15
Totaal activa	31
BTW (P)	0
rsz	18
Vennootschapsbelasting (P)	0
Totaal passiva	18

Final result for 02/04/2018:

- Here the BTW (P) has only a booking on account number 61000 so it will display fine just like all the rest who only have 1 account number. As soon as there is a combination of account numbers for a certain heading it won't display

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

Also: the subtotals "totaal activa" and "totaal passiva" were empty

[sunny_talwar · Accepted solution]

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

[axana___]

It worked! Wow, thank you so much for your help!!

[andreacossu1]

Hi,

This solution doesn't seem to work for me.  I would like to make a balance sheet i.e. a cumulative amount where I have a pivot table with Reporting Period as a column and Account as a row.  Note, in addition I have a number of other variables in both columns and rows.  The basic functionality should just make a cumulative total across Reporting Period for each of the other variables shown.

e.g. given underlying data of:

Account, Reporting Period, Amount

Premium, 2019, 10

Premium, 2021, 15

I would like to show:

Premium, 2019, 10

Premium, 2020, 10 (10+0)

Premium, 2021, 25 (10+0+15)

The problem is with the missing row in 2020 for example.