Filter Non offsetting entries

[Mzaheer24]

Could you please advise how to Filter entries which can not be offset

[GrowthNatives]

Hi @Mzaheer24,

Thanks for sharing the sample file!.

To filter entries that cannot be offset (i.e., their total amount per AC doesn't sum to zero), here's how you can solve it in Alteryx:

Steps: 

1. Input the data into Alteryx.

2. Use a Multi-Row Formula Tool to fill down the AC field where it's blank:

IF ISNULL([AC]) THEN [Row-1:AC] ELSE [AC]

     3. Use a Summarize Tool:

• Group by AC
• Sum Amount

     4. Use a Join Tool to join the summarized totals back to the original data on AC.

     5. Use a Filter Tool to keep only those where the sum of Amount ≠ 0:

[Sum_Amount] != 0
​

These are your entries that cannot be offset, as the amounts don’t balance out under the same account.

⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀

[Mzaheer24]

I also want to remove same sign entries that can not be offset . Could you please help me on this 

[GrowthNatives · Accepted solution]

Hi @Mzaheer24 ,

Sure, if you want to remove entries with the same sign (all positive or all negative) under the same AC, since they can’t be offset against each other, you can add this step before summarizing:

Steps to remove same-sign entries:

1. After filling down the AC values (using Multi-Row Formula)

2. Add a Summarize Tool:

   • Group by AC

   • Count of rows

   • Count of positive values: 

IF [Amount] > 0 THEN 1 ELSE 0

• Count of negative values:

IF [Amount] < 0 THEN 1 ELSE 0
​

   3. Use a Filter Tool to keep only groups where:

[Positive Count] > 0 AND [Negative Count] > 0
​

This means the group has both signs and can potentially be offset.

  4. Join the filtered ACs back to the main data to exclude same-sign groups.

This will ensure you're only working with ACs that have mixed-sign values — entries that can potentially be offset.

⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀