Solved: Re: Trial Balance - exclude negative accounts - Qlik Community

p_noindl · ‎2015-09-01

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

mphekin12 · ‎2015-09-01

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!

View solution in original post

Report Inappropriate Content · ‎2015-09-01

what about using the following expression - it will not include negative numbers

=if (sum(Amount)>0, sum (Amount),0)

p_noindl · ‎2015-09-01

that does not show the correct totals! Total for Group Bank should be 21.100.

Report Inappropriate Content · ‎2015-09-01

you are correct - interesting - if I convert to straight table and change expression total mode to sum of rows, it totals correctly but not when it is changed to a pivot

Report Inappropriate Content · ‎2015-09-01

it looks like some of the negative numbers are being excluded but not all. It appears the ones with a month of 1 are not being included but the ones with a month of 2 are?

Should the real number be higher that 21100?

If you crate a table box with all dimensions and select line 1 for bank, you will see all of the values and there are more than just the 2 negative -2000 values

Report Inappropriate Content · ‎2015-09-01

Hi ,

refer this . its working correct .

Happy learning !

Regards,

Nitesh Chavan

p_noindl · ‎2015-09-01

An account total is always the summary of all bookings within a certain time frame, therefore the correct amount excluding the accounts totalling less than 0 should be 21.100.

It is a general problem in Qlikview that the option: sum of y values is not available in pivot table, only in straight tables. In this special case I need to use the pivot table.

mphekin12 · ‎2015-09-01

Try using the AGGR function with Pivot Tables. Look at the Pivot that I added called 'With AGGR'

p_noindl · ‎2015-09-01

Hi, many thanks. It works - you have any idea how it could work if you include the month dimension in the pivot table?

p_noindl · ‎2015-09-01

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?