topic Re: Trial Balance - exclude negative accounts in QlikView

Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 15:26:42 GMT

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

Re: Trial Balance - exclude negative accounts

Tue, 01 Sep 2015 15:34:54 GMT

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

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

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 15:40:02 GMT

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

Re: Trial Balance - exclude negative accounts

Tue, 01 Sep 2015 15:50:08 GMT

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

Re: Trial Balance - exclude negative accounts

Tue, 01 Sep 2015 17:22:41 GMT

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

Re: Trial Balance - exclude negative accounts

Tue, 01 Sep 2015 17:58:29 GMT

Hi ,

refer this . its working correct .

Happy learning !

Regards,

Nitesh Chavan

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 18:27:29 GMT

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.

Re: Trial Balance - exclude negative accounts

mphekin12 — Tue, 01 Sep 2015 18:38:10 GMT

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

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 19:59:31 GMT

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

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 20:00:27 GMT

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?

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 20:27:08 GMT

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?

Re: Trial Balance - exclude negative accounts

mphekin12 — Tue, 01 Sep 2015 20:38:23 GMT

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!

Re: Trial Balance - exclude negative accounts

p_noindl — Tue, 01 Sep 2015 20:48:25 GMT

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