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 

What's the calculation behind converting to percentage?

[umilnje]

Here's the SQL code I used (redacted for privacy, so hopefully this makes sense). This is how I was able to get to the percentages in Excel (but not in Alteryx... have not figured out that piece yet), but like I said, the SQL code in Excel is based on several other columns: 

SELECT DISTINCTROW [Dataset1].[AcctNum], Sum([Dataset1].[Total Seats]) AS [Sum Of Total Seats], Sum([Dataset1].[Used Seats]) AS [Sum Of Used Seats], Sum([Dataset1].[Used Seats])/Sum([Dataset1].[Total Seats]) AS pct FROM [Dataset1] GROUP BY [Dataset1].[AcctNum];

Basically, I needed to dedupe the account number, then take the total number of seats and total number of used seats, then divide in order to obtain the percentage of used seats.

Is that what you are asking for?