COUNTIFS Function in Alteryx?

[umilnje]

Hi everyone,

I have a dataset in which I need to group a column of numbers in the following way:

0

<=0.34

>0.34&<=0.66

>0.66&<=1

I essentially need counts from this column in each grouping so I can then determine the percent of each group in the column. I used Excel's COUNTIFS function for each group: 

=COUNTIFS(D:D,"<="&H10) [this gives me the count of "0" in the column]

=COUNTIFS(D:D,">"&G11,D:D,"<=&H11) [this gives me the count of number > than and less than or equal to .34]

=COUNTIFS(D:D,">"&G12,D:D,"<="&H12) [this gives me the count of numbers > than .34 and less than or equal to .66]

=COUNTIFS(D:D,">"&G13) [this gives me the count of numbers > than .66]

In Alteryx, I have the summarize tool which gives me the count for the column in question, but now I'm stuck on how to get the column into these groupings.

I'm happy to provide any additional information. Thank you for your help!

Jenn

[atcodedog05]

Hi @umilnje 

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

[umilnje]

Sure thing! Here's the output when I work this in Excel: 

Min	Max		Count	Percent
	0		224	25%
0	0.34		86	10%
0.34	0.66		162	18%
0.66			418	47%

Here is my workflow (so far) in Alteryx.

[atcodedog05]

Hi @umilnje 

Can you provide input data in excel file.

[umilnje]

Sure thing, attached is the column in question.

[umilnje]

The Excel column is already in percentage form. What I have in Alteryx so far is just the counts in the Summarize tool. So, I'd need the percentages of the counts as displayed in the Summarize tool and then group those percentages in thirds (0, less than .34, .34 to .66, .66 and up). I hope that makes sense. Thank you! 

[atcodedog05]

Hi @umilnje 

Here is how you can do it.

Workflow:

Hope this helps : )

[umilnje]

This is great. I believe I gave you the wrong column, though. I'm sorry. I should have given you the column with the counts because that's what I have in Alteryx. In Excel, I worked out the percentages by using a SQL query in Access. My goal is to create a workflow in Alteryx that will mimic what I have in Access, but can be used by my colleagues. So, attached is the column with counts (not percentages). I'd need these counts changed into percentages, and then grouped by the buckets I identified above (I'm sorry!).

[atcodedog05]

Hi @umilnje 

How can I do the categorization and percentage calculation?

[umilnje]

That's what I'm trying to figure out in Alteryx. So, I have my column of counts, but I'd need those changed to percentages and then those percentages grouped by '0', <.34, >.34 & <=.66, >.66.

Does that make sense? Or were you asking about the SQL code I used to get the percentages in Excel? If so, that code is based on a few other columns that I cannot provide here.