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

    Counting Status Occurrences in Calc Field

    Len Van Den Berg

      Hi all


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

      For our example: Servername, Status

      Server1, RED








      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.


      Business Logic:


      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 2 Previous Next