topic Re: Qlik sense Balance Sheet in App Development

Qlik sense Balance Sheet

axana___ — Sat, 16 Nov 2024 21:27:12 GMT

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

Re: Qlik sense Balance Sheet

dplr-rn — Thu, 07 Feb 2019 13:54:40 GMT

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

Re: Qlik sense Balance Sheet

axana___ — Thu, 07 Feb 2019 14:00:04 GMT

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.

Re: Qlik sense Balance Sheet

dplr-rn — Thu, 07 Feb 2019 14:10:15 GMT

Can you give a sample app? i can help then

Re: Qlik sense Balance Sheet

axana___ — Thu, 21 Feb 2019 17:51:10 GMT

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

sunny_talwar — Fri, 22 Feb 2019 12:35:06 GMT

@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?

Re: Qlik sense Balance Sheet

axana___ — Fri, 22 Feb 2019 15:29:03 GMT

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

Re: Qlik sense Balance Sheet

sunny_talwar — Fri, 22 Feb 2019 16:49:31 GMT

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

Re: Qlik sense Balance Sheet

axana___ — Mon, 25 Feb 2019 09:21:30 GMT

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?

Re: Qlik sense Balance Sheet

axana___ — Mon, 25 Feb 2019 11:15:11 GMT

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.

Re: Qlik sense Balance Sheet

sunny_talwar — Tue, 26 Feb 2019 19:47:14 GMT

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

Re: Qlik sense Balance Sheet

axana___ — Wed, 27 Feb 2019 08:16:34 GMT

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

Re: Qlik sense Balance Sheet

sunny_talwar — Wed, 27 Feb 2019 12:51:02 GMT

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

Re: Qlik sense Balance Sheet

axana___ — Wed, 27 Feb 2019 13:26:19 GMT

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

Re: Qlik sense Balance Sheet

andreacossu1 — Mon, 27 May 2019 09:39:44 GMT

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.