Integrate data from other Dim in a Straight Table - Accounting

[aviadbein]

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:

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

[sunny_talwar]

Try this

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

[aviadbein]

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

[sunny_talwar · Accepted solution]

Try this

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

[aviadbein]

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