1 2 Previous Next 17 Replies Latest reply on May 17, 2018 5:27 AM by Okechukwu Ossai

# Counting Status Occurrences in Calc Field

Hi all

I am working with a Batch Status CSV containing a few fields:

For our example: Servername, Status

Server1, RED

Server2,GREEN

Server3,GREEN

Server4,GREEN

Server5,AMBER

Server6,GREEN

Server7,GREEN

We identify the Status with  Text values as "GREEN" when the run  has completed, "AMBER" when it is still running and "RED" it has not started yet or failed.

So we have a CSV that refreshes every 30 mins for the Managers to monitor the Batch Updates

I am doing a Dashboard showing various levels of Information.

For this question we discuss only Level One = The Overall Status for the Total Batch.

If any one Status in the Batch is Red, ALL OF THE BATCH STATUS is RED  like - (IF COUNT([Status]="RED") > 0)  Then "RED"

If there is no Red Status, but one of the Status is AMBER, then a Overall Batch is defined as Amber. (IF COUNT([Status]="AMBER") > 0)  and COUNT(STATUS)= "RED" = 0 Then "AMBER"

If The status in the CSV is Green , no red and no Amber the Overall status is defined as Green. ( ELSE "GREEN" END)

My Approach: I want to add the Calaculated field (BatchStatus) when uploading the CSV as a TDS, where I count the number of Reds and Ambers and attempt to set the BatchStatus as Either Red, Amber or Green depending on the Business Logic

My Question: I am struggling with the Formula with this.

How would I get the desired BatchStatus Value based on the Logic stipulated above?

• ###### 1. Re: Counting Status Occurrences in Calc Field

Hi Len,

IF SUM( IF [Status]="Red" THEN 1 END)>0 THEN "Red"

elseif  SUM( IF [Status]="Red" THEN 1 END)=0

AND SUM( IF [Status]="Amber" THEN 1 END)>0

THEN "Amber"

ELSE "Green"

END

Regards,

Rahul

• ###### 2. Re: Counting Status Occurrences in Calc Field

Hi Len,

There are many ways to do using LOD. One approach is the code below;

Create calculated field [Batch Status]

IF {FIXED [Servername]: MAX([Status])} = {FIXED [Servername]: MIN([Status])} AND {FIXED [Servername]: MAX([Status])} = "GREEN" THEN "GREEN"

ELSEIF {FIXED [Servername]: MAX([Status])} = "RED" THEN "RED"

ELSEIF ({FIXED [Servername]: MAX([Status])} = "GREEN" AND {FIXED [Servername]: MIN([Status])} = "AMBER")

OR ({FIXED [Servername]: MAX([Status])} = "AMBER") THEN "AMBER" END

If you want to get the individual counts, you could do something like the code below for each status

[# of RED Status]

COUNTD(IF [Batch Status] = "RED" THEN [Servername] END)

Do the same for other statuses.

• ###### 3. Re: Counting Status Occurrences in Calc Field

Hi Len

I included a Batch column in the data - you can use a Datetime etc depending on your data or not use it all.

I wanted to test a few different combinations to test

iif

({ fixed [Batch] : sum( if [Status]=' RED' then 1 else 0 end )}>0,

'RED',

iif(

{ fixed [Batch] : sum( if [Status]='AMBER' then 1 else 0 end )}>0,

'AMBER',

'GREEN'))

J

• ###### 4. Re: Counting Status Occurrences in Calc Field

Hi Rahul

I have tested this and find it makes all values "GREEN" disregarding the 1 RED and 1 AMBER.

Ideally I would want to see BatchStatus all = "RED" because we have 1 RED.

If we had 0 RED it would be AMBER.

If we had 0 AMBER and 0 RED it would be GREEN.

Thank you for trying.

• ###### 5. Re: Counting Status Occurrences in Calc Field

Thanks for this attempt.

Unfortunately this returned null values in the BatchStatus column.

• ###### 6. Re: Counting Status Occurrences in Calc Field

There are a number of ways to do this. Key to all is to convert the colour to a number based on importance. You either want the most important to be the largest or lowest number so can be identified by either Min or Max. For your example create this field (with Red being the min):

[StatusNum]

CASE [Status]

WHEN 'RED' THEN 0

WHEN 'AMBER' THEN 10

WHEN 'GREEN' THEN 20

ELSE 100

END

Table Calc

Depending on how you want to use the results you could use a table calculation.

[OverallStatusNum]

WINDOW_MIN(MIN([StatusNum]))

You can use that to convert back to the colour word if you wish.

IF [OverallStatusNum] = 0 THEN 'Red'

ELSEIF [OverallStatusNum] = 10 THEN 'Amber'

ELSEIF [OverallStatusNum] = 20 THEN 'Green'

ELSE 'Other'

END

LOD

Same as above but the OverallStatusNum can change to:

{ FIXED : MIN([StatusNum])}

• ###### 7. Re: Counting Status Occurrences in Calc Field

Did you run it exactly as provided? I don't see the reason why it should return null. The code should work for you. Did you try it line by line to see if any part of the code works. For example, if you the first line of the code;

IF {FIXED [Servername]: MAX([Status])} = {FIXED [Servername]: MIN([Status])} AND {FIXED [Servername]: MAX([Status])} = "GREEN" THEN "GREEN" END.

Does it return some "GREEN" status?

I suggest you post a sample dummy workbook, so a more direct solution can be provided.

• ###### 8. Re: Counting Status Occurrences in Calc Field

Trying to look at our workbook - My apologies - I should have mentioned - I am on Desktop Pro 10.0

• ###### 9. Re: Counting Status Occurrences in Calc Field

Are the servers grouped in batches? Do you have any field called [Batch]? Like batch 1, batch 2 etc.

• ###### 10. Re: Counting Status Occurrences in Calc Field

Hi all

I attach a test workbook for you to see what I am doing...

I am using Tableau 10.0 Desktop Pro.

For those using later versions you may run into a few prompts...

Please note the Calculated Field in the TDS.

Kind regds

Len

• ###### 11. Re: Counting Status Occurrences in Calc Field

Hi Len,

What is the expected result and in what format do you want to display the result?

Ossai

• ###### 12. Re: Counting Status Occurrences in Calc Field

Hi Ossai

I want to create a Calculated field called BatchStatus and the outcome has to be as follows:

Server, Status, BatchStatus

Server1, RED,RED

Server2,GREEN,RED

Server3,GREEN,RED

Server4,GREEN,RED

Server5,AMBER,RED

Server6,GREEN,RED

Server7,GREEN,RED

If the Server1 is updated 30 mins later to GREEN, the Formula in the Claculated field must change the table to:

Server, Status, BatchStatus

Server1, GREEN,AMBER

Server2,GREEN,AMBER

Server3,GREEN,AMBER

Server4,GREEN,AMBER

Server5,AMBER,AMBER

Server6,GREEN,AMBER

Server7,GREEN,AMBER

• ###### 13. Re: Counting Status Occurrences in Calc Field

Hi Len,

Create calculated field [BatchStatus]

IF ({MAX([Status])} = "GREEN" AND {MIN([Status])} = "AMBER") OR ({MAX([Status])} = "AMBER") THEN "AMBER"

ELSEIF {MAX([Status])} = "RED" THEN "RED"

ELSEIF {MAX([Status])} = {MIN([Status])} AND {MAX([Status])} = "GREEN" THEN "GREEN" END

This should do it.

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 14. Re: Counting Status Occurrences in Calc Field

I have provided a solution based on the data you provided. However, the explanation of your expected result doesn't match the data in the packaged workbook.

If your actual data structure is different from what you attached then the solution will not work. If it doesn't, you will need to re-attach a new dummy dataset. It doesn't need to be real just fake data but it must reflect the structure and complexity in your actual dataset.

1 2 Previous Next