Counting Across Columns

Question

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:

StockClient 1Client 2Client 3Client 4ABC.05.010.07XYZ.060.01.03123.2.08.060

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 Answer

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 Answer

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,