Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aviadbein
New 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

Re: Integrate data from other Dim in a Straight Table - Accounting

Try this

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

5 Replies

Re: Integrate data from other Dim in a Straight Table - Accounting

Try this

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

aviadbein
New Contributor II

Re: Integrate data from other Dim in a Straight Table - Accounting

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

Re: Integrate data from other Dim in a Straight Table - Accounting

Try this

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

Highlighted
aviadbein
New Contributor II

Re: Integrate data from other Dim in a Straight Table - Accounting

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!

Re: Integrate data from other Dim in a Straight Table - Accounting

Super