# Qlik Sense App Development

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

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 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
Labels (5)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

## 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

## 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
Highlighted
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

## Re: Qlik sense Balance Sheet

Can you give a sample app? i can help then
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).

MVP

## 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

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

New Contributor

## Re: Qlik sense Balance Sheet

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
MVP

## 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])```
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?

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.