community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Output Unique Combinations with SumIFs

Meteor

I am new to Alteryx, so struggling to find a solution elsewhere on the message board.  I have two columns "Category" and "Ledger Name" with anywhere from 5 - 15 distinct values in them. 

 

Example below:

Category            Ledger Name

PPE                     US Ledger

PPE                      US Ledger

PPE                      Canada Ledger

Prepaid                China Ledger

Prepaid                Germany Ledger

Goodwill                 US Ledger

 

 

I want an output that finds all the unique values in both columns and then creates two columns in the following format below:

 

Category            Ledger Name                  Closing Balance

PPE                    US Ledger                         A sumifs formula based on both Category / Ledger name on another tab output

PPE                     Canada Ledger                A sumifs formula based on both Category / Ledger name on another tab output

PPE                    China Ledger                    A sumifs formula based on both Category / Ledger name on another tab output

PPE                    Germany Ledger              A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              US Ledger                         A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              Canada Ledger                 A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              China Ledger                    A sumifs formula based on both Category / Ledger name on another tab output

Prepaid              Germany Ledger               A sumifs formula based on both Category / Ledger name on another tab output

 

 

I have tried cross-tab and the unique tools, but was not able to determine which was correct or what combination will get me here.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @taxguy33 

 

Do you have a sample of your data from your input and desired output just to make it more clear?

 

Cheers,

Nebula
Nebula

Hi @taxguy33 

 

Here's a possible solution

 

WF.png

 

It starts by finding the unique categories and ledgers and performing a cross join using the Append Fields tool.  This is joined back to the original data on Category and ledger name.  The joined records are summarized and the unjoined records gets a 0 Closing Balance before being unioned and sorted

 

This input data

Input.png

gives this output

Results.png

Dan

Labels