Counting Across Columns

[cmohyi]

Hi all,

I'm trying to find a dynamic way to count the number of specific values across columns. Here's a sample of the data:

Stock	Client 1	Client 2	Client 3	Client 4
ABC	.05	.01	0	.07
XYZ	.06	0	.01	.03
123	.2	.08	.06	0

I'd like to create a new column with a count of the instances of 0. I don't want to use the formula tool because my column headers have account numbers rather than Client 1, Client 2, etc and those account numbers will change regularly. I think the multi-row formula or multi-field formula might help but I haven't been able to get those to work.

Thank you all very much in advance!!

[Kenda · Accepted solution]

Hey @cmohyi 

I have attached a workflow that should get you the result you're looking for. I added a couple rows and changed the data a little bit to try and account for different circumstances.

First transpose your data, use a Summarize tool to count the instance of each value for each stock for each name. Use the Filter to only keep the 0 values. Use a Cross Tab to get the data oriented correctly again. Lastly use a Join then a Union to get your final output.

Hope this helps!

[Thableaus · Accepted solution]

Hi @cmohyi 

The solution is very simple:

- The secret when you want to dynamic Summarize something is to transpose your data.

- Transpose data (Key Field should be Stock, I assume) - (Data Fields - Check Dynamic/Unknown Fields)

- Create a Flag to indicate if your Value field is 0 (Flag 1 or 0)

- Group by Stock and Sum the zeroes

- Join it back to the dataset

WF appended.

Cheers,