6 Replies Latest reply on Aug 19, 2016 9:58 AM by Keith Johnston

# Count same values in multiple fields

Hello All,

Hoping the group can help with this. I currently am using Tableau Desktop, Personal Edition.

In the attached Excel file, I have two data sources (One is a list of Orders, the other a list of Rules). Multiple Rules are applied to each Order, with different Rule Results.  I wanted to ask two questions:

1. What would be the easiest method to count the number of times a specific rules. The challenge is that the same rule can appear in multiple columns, as shown in the Source 1 tab.

2. Similarly, I'd also like to count the Rule Results, by Rule

The Solution tab contains my intended result, would like to achieve in the most efficient way possible. Any input is greatly appreciated!

Thank You!

KJ

• ###### 1. Re: Count same values in multiple fields

Pivot your data.  Have multiple rows per order.  You'll have x-many rows for an order, where X is the number of rules applied.  For each rule row, have the results in that row.

• ###### 2. Re: Count same values in multiple fields

Thank you Joe. How would I setup/control the "X rows per order" as you mentioned?

Would it be possible to convert my Example file into Packaged Workbook, so I could follow?

• ###### 3. Re: Count same values in multiple fields

Attached is the modified excel file, and an 8.2 workbook.

You don't even need the second data source for this.

1 of 1 people found this helpful
• ###### 4. Re: Count same values in multiple fields

Thanks again for providing the examples, I can use this in certain instances.

My example was representative of a much larger data source ( ~500,000 records).

If it wasn't possible to have multiple rows per order (i.e. a one order per row restriction), what would the Tableau solution work around look like?  Apologies for not mentioning previously.

• ###### 5. Re: Count same values in multiple fields

Probably you would have everything calc-field driven, with (in your example) 4 levels of IF logic (and maybe 24:  (4x3x2x1)) to handle all the permutations in those calcs.  (And if you could have 5 rules, or 6, etc., it would grow in complexity accordingly.)

Don't do that to yourself.

Tableau is made to SING with data in the proper shape.  Don't worry about 2 million records.

With data in the proper shape, I whipped up that example in 5 minutes.  It took me longer to hack up the excel sheet than to write the tableau viz.  (And maybe someone better versed in excel could have done that with some pivot function in just a few keystrokes!)

1 of 1 people found this helpful
• ###### 6. Re: Count same values in multiple fields

I had actually tried to create a variation of what you described, and it wasn't pretty. And the underlying DB has up to 15 such rules per order, so you've validated my results of exploring that path.

I agree, Tableau is awesome if the data in the right shape, so I'll work on re-shaping it.

Appreciate the help!