Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 (4)
1 Solution

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

View solution in original post

14 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

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
Partner - Master III
Partner - Master III

Can you give a sample app? i can help then
axana___
Contributor
Contributor
Author

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

image.png


@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

image.png

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

axana___
Contributor
Contributor
Author

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

BTW (A)5
Cash10
Vennootschapsbelasting (A)15
Totaal activa 
BTW (P)0
rsz18
Vennootschapsbelasting (P)0

 

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

BTW (A)0
Cash2
Vennootschapsbelasting (A)0
Totaal activa 
BTW (P)-4
rsz18
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___
Contributor
Contributor
Author

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

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.

Problem account numbers.PNG