Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
axana___
Contributor
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
Labels (3)
14 Replies
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___
Contributor
Contributor
Author

Final result for 02/03/2018:

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

BTW (A)6
Cash10
Vennootschapsbelasting (A)15
Totaal activa31
BTW (P)0
rsz18
Vennootschapsbelasting (P)0
Totaal passiva18

 

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
Cash2
Vennootschapsbelasting (A)0
Totaal activa2
BTW (P)-4
rsz18
Vennootschapsbelasting (P)-7
Totaal passiva7

 

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

sunny_talwar

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___
Contributor
Contributor
Author

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

andreacossu1
Contributor
Contributor

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.