Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Sumif with Unique Values

JasmineG
5 - Atom

I have 2 sets of data, let's say X and Y. X is a set of ID numbers and Y is a set of principal balances. I need to first sum all of the Y values that correspond to each X and then count each of those results that are equal to zero for a unique X.

 

For Example:

X: ID Number

Y: Balance
Z: Sum of each "Y" under the unique "X"

 

Screenshot_1.png

After calculating Z, I will need to count each unique X that has a Z of zero. In the above case, it would be 1.

 

In excel, I can use a Sumif to calculate Z (SUMIF(X,UNIQUE'X,Y) where I just removed duplicates in a separate tab to arrive at "UNIQUE'X". After calculating Z, I created a flag: If(Z=0,1,0) and did a count function for all of those cells. How do I do this in alteryx?

Note that this is a very simplified version of my dataset to ease the explanation. 

2 REPLIES 2
Claje
14 - Magnetar

Hi,


This is a quick example of how to do something like this.


Basically, you can use the Summarize tool and the Join tool to create your Z value, and then use a Filter tool and a Summarize tool to count the distinct set of RecordIDs which have Z = 0

 

Hope this helps!

JasmineG
5 - Atom

That worked after I eliminated "0" X's through a filter tool! Thank you so much!!

Labels