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: 
aviadbein
Contributor II
Contributor II

Integrate data from other Dim in a Straight Table - Accounting

I all,

I have developed a Profit & Loss and Balance Sheet following this two posts: https://community.qlik.com/docs/DOC-3324‌ and https://community.qlik.com/blogs/qlikviewdesignblog/2012/08/10/how-intervalmatch-solved-my-profit-an...

and it's all work perfect except one issue.


I'm having a problem with integrating the Income Statement (Profit & Loss) and Balance Sheet.

an illustration of my need:

Illustration.PNG


basically, I need to display a value from a Dim other than the one that my table is bound to.


If I'll be more specifically, as you can see, the value "Net Loss" (Profit & Loss Dim) is calculated on the P&L data and I want to integrate this value in a straight table of Balance Sheet Dim.

I have tried with set analysis, something like this:

if(BalanceSheetHeading='Net Loss', sum({<FinancialStatement={'P&L'},ProfitLossHeading={'Net Loss'}>}BALANCE), sum(BALANCE))

but unfortunately without success so far. any ideas?

Thanks in advance,

Aviad

1 Solution

Accepted Solutions
sunny_talwar

Try this

If(Only({1} BalanceSheetHeading) = 'Net Loss', Sum(TOTAL {<FinancialStatement={'P&L'}, ProfitLossHeading = {'Net Loss'}, Year>} BALANCE), Sum(BALANCE))

View solution in original post

5 Replies
sunny_talwar

Try this

If(BalanceSheetHeading = 'Net Loss', Sum(TOTAL {<FinancialStatement={'P&L'}, ProfitLossHeading = {'Net Loss'}>} BALANCE), Sum(BALANCE))

aviadbein
Contributor II
Contributor II
Author

It works! BUT, when i select date, let's say year=2017, it become 0. Why so?

sunny_talwar

Try this

If(Only({1} BalanceSheetHeading) = 'Net Loss', Sum(TOTAL {<FinancialStatement={'P&L'}, ProfitLossHeading = {'Net Loss'}, Year>} BALANCE), Sum(BALANCE))

aviadbein
Contributor II
Contributor II
Author

Great!

I just made a little change:

if(Only({1} BalanceSheetHeading) = 'Net Loss',(sum(total {<FinancialStatement={'P&L'},ProfitLossHeading ={'Net Loss'},Q=,Month={"<=$(=num(max(Month)))"}>}BALANCE),sum(BALANCE))

Thanks a lot Sunny!

sunny_talwar

Super