Countif function

[Jenina]

Hello All,

I am trying to do a simple countif in Alteryx but it looks like it's not straightforward. 

I just wanted to count the currencies per Account unit. Please see below sample. Thanks for the help.

Source:

Account unit	Currency
UNIT 113	EUR
UNIT 113	EUR
UNIT 113	EUR
UNIT 113	USD
UNIT 113	USD
UNIT 112	EUR
UNIT 112	EUR
UNIT 112	USD
UNIT 112	USD
UNIT 112	USD

Output:

Row Labels	A Account	B Account	Grand Total	Item Count
EUR	-165522.27	192850.67	27328.4
unit 113	31652.24		31652.24	3
unit 112	-65.64	-0.01	-65.65	2
USD	-165522.27	192850.67	27328.4
unit 113	31652.24		31652.24	2
unit 112	-65.64	-0.01	-65.65	3

[geraldo]

@Jenina 

use the summarize tool group by Account unit Currency and count Currency

[Jenina]

@geraldo  sorry, forgot to mention I also need to consider the A account and B account criteria,  I need the total count of currency per account unti existing in both A and B accounts. That's where I am having issues. 

[apathetichell]

You can do that all in the summarize tool. group by both fields. count of either fields. sum of your sum columns.

[Jenina]

@apathetichell  do you have a sample workflow? source below. What I am getting is the total per GL account, when I tried to sum it is not getting the correct count.

Account unit	Currency	GL Account
UNIT 113	EUR	A Account
UNIT 113	EUR	B Account
UNIT 113	EUR	B Account
UNIT 113	USD	A Account
UNIT 113	USD	B Account
UNIT 112	EUR	A Account
UNIT 112	EUR	B Account
UNIT 112	USD	A Account
UNIT 112	USD	B Account
UNIT 112	USD	A Account

[apathetichell]

post some sample data -and I can mock something up. - this sounds like you need to group by an additional field. - you will need to do two different breakdowns and some kind of join. Having two different sub total types (by currency and by account - at least in the format you have) would require some pivots/joining.

[Jenina]

@apathetichell  I attached a sample data. Thanks!

[jdminton · Accepted solution]

This should work for you. Unfortunately, it does take more steps to do this in Alteryx.

[Jenina]

@jdminton  I am trying this now, could you pls send me the input you used, thank you!

[jdminton]

@Jenina I used the one you provided ;) I did start the data on line 2 though in the configuration.