Creating a report showing "balance != 0" from journal entries

Apologies in advance. This is probably no brainer stuff, but I am brand new to Alteryx. I am basically exporting a data dump from our accounting journals. My end result goal will be to show journal accounting details for control numbers that do not balance to 0, sorted by control number.

My progress so far is as follows:

- filtering [journal] and [detail desc]

- sorting by [control]

- filtering duplicate data [control] - had to use a custom macro from this board in order to get this to work, since the built in unique tool was not filtering truly duplicate data.

- summarize, group by [control], and sum [posting amt] to create a new column field [balance]

This is where I am stuck. I am not sure the best way to go about this. My end result should be to remove all the posting detail that has a 0 balance, and keep all the posting detail that != 0.

Thanks in advance for your help.

First Danny project - Inv != 0.yxmd

Output

Accepted answers

mceleavey

Hi @dannybass ,

I think you just need to change the union to be a join which will be joined on Control, if I'm understanding correctly:

All comments

mceleavey

Hi @dannybass ,

You just need to apply a filter and take the True (T) output:

As for the other things, the unique tool does filter truly duplicate data, but depends on the fields you select.

dannybass

I tried the above in my earlier experimenting (I should have mentioned this, my apologies). When I ran it, it just removed the zero balances that were my [balance] = 0 lines (essentially the subtotal rows). I assumed this was corrected since [balance] = null on the rows that had the details.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703