1 Reply Latest reply on Jul 19, 2013 1:54 PM by Jonathan Drummey

# Counting dimensions with conditions

Hello fellow Tableau users,

I am tying to count dimensions that has color conditions and having some troubles.

Bascially, I have colour conditions set up on dimentions and I want to count them and do some calculation based on # of those dimensions falling into different colour categories.

In the example, I would like to create another worksheet that creates soemthing simmiar to the attached image so I can put them both in one dashboard.  Counting how many stores fall into different colours based on '2013 sales coloum'. so there are 2 stores in green, 0 in yellow and 1 in red.  And next colum just showing % share, wihch is the dividing by total # of stores (3 in this case but both # of stores by color and total could be more as I want to make it interactive).

I tried with count and countd function to create distintive measures and also tried to replicate with sales color condition fields but haivng some hurdles... any suggestions would be apprecaited.

Thanks,

Jason

• ###### 1. Re: Counting dimensions with conditions

Hi Jason,

The color condition you have set up is not a dimension, instead it is a discrete (blue pill) measure. See http://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/ for a more detailed explanation.

This gets tricky because the KPI color is based on a table calculation, so the view needs to have enough detail to enable that computation (and then any following computations) to occur. And I'm presuming not every KPI color for every measure is guaranteed to have the range of Red/Yellow/Green values every time, which means that we have to do our own "padding" to make sure that the values come about.

I set this up for one measure (Sales) in the attached, where there are three nested table calculations that get the store counts for Red/Yellow/Green, and then Measure Names can be used to generate the highlight table. I also added two table calculation filters, one to reduce marks and the other to hide the 2012 data. Using this technique, you'd need to generate an additional worksheet for the % measures, and then if desired 2 additional worksheets for other measures. Then you could do things like hide headers and put the worksheets together within a horizontal layout container in a dashboard. I don't know your data volumes, this could get pretty slow in terms of update times.

Alternatively, I'd be tempted to create this view by first working with the datasource to do more pre-computation, for example getting to the point where the YoY computation is done using a a regular aggregate or even done in the data source. Then there could be some scaffolding done to create false "Measure Names" that the results could be plugged into. Joe Mako did a Think Data Thursday on building scaffold sources a few weeks back: http://community.tableau.com/thread/126855.

That said, this could be a lot easier to create with something like this idea: http://community.tableau.com/ideas/1604.

Jonathan