Trial Balance - exclude negative accounts

[p_noindl]

Hi,

In balance sheets bank accounts change their group from assets to liabilities in case of negative balances. In my example I am trying to exclude the accounts with negative balances and to show the right sums per group, which would be in this case for the 'bank' group of accounts 21.100.

Anyone has an idea how to solve this so that it works through all dimensions in a pivottable and the correct sums are shown.

Maybe my thinking was to complex.

Many thanks

Peter

[p_noindl]

Hi, it works, many thanks. Would you have any idea how it could work if you add a month dimension and have a sum over the month?

[mphekin12 · Accepted solution]

Peter,

You might get inaccurate data once you bring in the month.  In the original example, Account 1015 would be excluded because the total account amount was negative.  If you add the Month as a dimension, there are months where the amount is positive, even though the total account is negative.  See the screen shot below for account 1015:

Here are the two charts, the original with just the Line, Group and Account and the new one with the additional Month dimension, for comparison:

You can clearly see how a couple of accounts made the list on the second chart that really shouldn't have as per your specs listed above.

Hope this helps!

[p_noindl]

Hi, many thanks. I know that the month-dimension is not possible but thought that you would have an idea to separate month results from total results (all 3 month).

Anyway this was great help because I went into the wrong direction.

Best regards