5 Replies Latest reply on Jun 25, 2016 9:43 PM by Mahfooj Khan

# Tableau - Calculated Field : repeat count when multiple conditions are met

This is a column

Status  Count

A B C   1000

A B     500

B D     200

C D     100

I have a column named Status where each row contains all the different status taken by a person.

I'm trying to create a calculated field which should give the following table

Status|  Status A|  Status B|  Status C|  Status D|

A B C |      1000|      1000|      1000|         0|

A B   |       500|       500|         0|         0|

B D   |         0|       200|         0|       200|

C D   |         0|         0|       100|       100|

Here's an attempt

IIF(CONTAINS([Status],"A"),"Status A",

IIF(CONTAINS([Status],"B"),"Status B",

IIF(CONTAINS([Status],"C"),"Status C",

IIF(CONTAINS([Status],"D"),"Status D","")))

The returned result using the calculated field above is however:

Status|  Status A|  Status B|  Status C|  Status D|

A B C |      1000|         0|         0|         0|

A B   |       500|         0|         0|         0|

B D   |         0|       200|         0|         0|

C D   |         0|         0|       100|         0|

Can someone help me out with this?

I truly believe the solution would be simple.

Thanks!

Message was edited by: Afiq Johari updated results presentation.

• ###### 1. Re: Tableau - Calculated Field : repeat count when multiple conditions are met

Hi,

You can use the split function and do like this:

• ###### 2. Re: Tableau - Calculated Field : repeat count when multiple conditions are met

As Tharashasank Davuluru suggested you've to perform the split on your original data source and then copy all the data and paste it on a new excel workbook. Bring the data back in Tableau and do a pivot on the splits:

You can go through below link where I've explained how to achieve this.

Although this is not the best way to do it because if the data changes at the source level, you will have to re-do this process. If there is however, a one time need to show the data the way you described, this might just work fine.

Let me know If this help.

Mahfooj

• ###### 3. Re: Tableau - Calculated Field : repeat count when multiple conditions are met

Walk through

Split

Copy data in excel

again conect excel data in tableau and pivot

Delete unwanted columns

Change pivot field vlaues name to "Status" and put data source filter to ignore null values.

workbook version 9.2

Mahfooj

• ###### 4. Re: Tableau - Calculated Field : repeat count when multiple conditions are met

Thanks both, really appreciate your time to answer this question. It's a good solution.

However, I'll have this data extracted directly from the server and a dashboard is needed as well. I'm still trying to figure out how can this be done within Tableau namely through calculated field.

This should allow automation as we refresh the data for the dashboard.

Thanks again!

• ###### 5. Re: Tableau - Calculated Field : repeat count when multiple conditions are met

Hi Afiq,

Sorry for late reply. Is that what you wanted?

I've created few calculated fields for Status A, B, C and D like this.

then drag status dimension in rows shelf and drag measure names in column shelf and filter only these measures.

Let me know If this help.

Workbook (public version 9.3) has been published in my public profile

Mahfooj