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: 
Not applicable

How to create a INCOMING (-) OUTGOING resulting in BALANCE in the same column expression?

How to create a INCOMING OUTGOING resulting in BALANCE in te same colum expression?

The 'Account' dimension can be Incomings, Outgoings and Balance. Balance is null() on script load.

I've been trying to do something like this, but it doesn't work at all. Please help. Thankyou anyway

=IF(Account='Balance', AGGR(Sum( {$<Account={'Incomings'}>}Value), Account,Year) - AGGR(Sum( {$<Account={'Outgoings'}>}Value), Account,Year),SUM(Value))

Capturar.PNG

1 Solution

Accepted Solutions
kuba_michalik
Partner - Specialist
Partner - Specialist

You were pretty close already. AGGR is not needed, but you need to ignore vertical chart dimension, because obviously Balance is not connected to any data by itself. So something like this:

=IF(Only(Account)='Balance', Sum( {$<Account={'Incomings'}>} TOTAL<Year> Value) - Sum( {$<Account={'Outgoings'}>} TOTAL<Year> Value),SUM(Value))

The <Year> part tells TOTAL not to ignore Year for the calculation. The "Only(Account)" part is not necessary, but it's good to make all aggregations explicit (just so it's obvious what is happening, it does not have any performance implications or anything).

View solution in original post

5 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi VITOR,

As a variant, if I understand you correctly. Source XLS attached (it follows the structure of your file).

Script code

Directory;

Table1:

LOAD @1 as Year,

     @2 as Balance

FROM

Balance.xlsx

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 15)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 9)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Transpose()

));

Result

3.jpg

Regards,

Andrey

kuba_michalik
Partner - Specialist
Partner - Specialist

You were pretty close already. AGGR is not needed, but you need to ignore vertical chart dimension, because obviously Balance is not connected to any data by itself. So something like this:

=IF(Only(Account)='Balance', Sum( {$<Account={'Incomings'}>} TOTAL<Year> Value) - Sum( {$<Account={'Outgoings'}>} TOTAL<Year> Value),SUM(Value))

The <Year> part tells TOTAL not to ignore Year for the calculation. The "Only(Account)" part is not necessary, but it's good to make all aggregations explicit (just so it's obvious what is happening, it does not have any performance implications or anything).

Not applicable
Author

Hi Jakub,

Its really working!! Thanks!!!

But there is something that i haven't said yet.

I need dynamic collumn on that chart. To do that i have a dynamic cycle group with Year,Month, Date. That group is called 'Calendar'. So i tried to swtich the statement TOTAL <Year> to TOTAL <[$(=Getcurrentfield([Calendar]))]> and it doesn't work.

Thank you!

Sorry for bad english by the way.

Not applicable
Author

thnx for that approach.

I need something more dynamic like Jakub solution

Thank you anyway!

Not applicable
Author

Sorry!!

I'm wrong.

Yeah It's work!