Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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).
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
Regards,
Andrey
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).
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.
thnx for that approach.
I need something more dynamic like Jakub solution
Thank you anyway!
Sorry!!
I'm wrong.
Yeah It's work!