8 Replies Latest reply on Sep 4, 2018 5:46 AM by Rajhesh Kumar A S A

    Conditional Formatting based on multiple conditions for multiple categories

    Rajhesh Kumar A S A

      Hi all,

       

        Need your support in achieving the Conditional Formatting, which has multiple conditions for multiple categories. I have 5 teams which has to assign fixed count of  tickets on weekdays and weekends. Count will vary team to team and also on weekdays and weekends. I need to highlight those count in RED, which are greater than the set threshold during weekdays and in Weekends. Below is the sample input and threshold levels

       

      TeamTicket NoAssigned date
      1st LATT1234509-Aug
      1st LATT1234609-Aug
      1st LATT1234710-Aug
      1st LATT1234811-Aug
      2nd LATT1234909-Aug
      2nd LATT1235009-Aug
      2nd LATT1235109-Aug
      TACTT1235210-Aug
      TACTT1235310-Aug
      TACTT1235410-Aug
      TACTT1235511-Aug
      1st LATT1235609-Aug
      1st LATT1235709-Aug
      2nd LATT1235811-Aug
      2nd LATT1235911-Aug
      TACTT1236012-Aug
      TACTT1236112-Aug

       

      Thresholds:

      During Weekdays, for 1st LA, count should not exceed 2, for 2nd LA, count should not exceed 1 and for TAC, count should not exceed 1

      During Weekends, for 1st LA, count should not exceed 1, for 2nd LA, count should not exceed 2 and for TAC, count should not exceed 2

       

      So, as said, the thresholds are differing for each teams and during weekdays & Weekends. If the team exceeds the threshold in terms of their count of tickets / per (weekday/weekend), then the cell should be highlighted with RED for the below output

       

      Team Name09-Aug10-Aug11-Aug12-Aug
      1st LA4110
      2nd LA3020
      TAC0312

       

      Thanks.

      Raj...

        • 1. Re: Conditional Formatting based on multiple conditions for multiple categories
          Zhouyi Zhang

          Hi, Rajhesh

           

          Please find my solution attached.

           

          Below is the screenshot of calculation field and the result.

           

           

          Hope this helps

           

          ZZ

          • 2. Re: Conditional Formatting based on multiple conditions for multiple categories
            Rajhesh Kumar A S A

            Hi, below is the same requirement with different teams and different thresholds, during weekdays and weekends. But when using the same calculation field formula, its not working. kindly correct me, where I'm going wrong. Below is the threshold set & output

             

            Threshold:

             

            TeamThreshold
              (Weekdays)
            Threshold
              (Weekends)
            FO RAN/Power6958
            FO TXN2918
            LOCKDOWN257
            FO LBS1714
            RTPM43

             

            Output:

             

            And here is the calculation field formula

             

            Code:

             

             

            IF DATENAME('weekday',[Assign Date]) = 'Saturday' OR DATENAME('weekday',[Assign Date]) = 'Sunday' THEN

                    IF [Assigned From (group)] = "FO RAN / POWER" THEN     

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 58 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    ELSEIF [Assigned From (group)] = "FO TXN" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 18 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    ELSEIF [Assigned From (group)] = "Lockdown" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 7 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                     ELSEIF [Assigned From (group)] = "FO LBS" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 14 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    ELSEIF [Assigned From (group)] = "RTPM" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 3 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    END

            ELSE

                    IF [Assigned From (group)] = "FO RAN / POWER" THEN     

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 69 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    ELSEIF [Assigned From (group)] = "FO TXN" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 29 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                     ELSEIF [Assigned From (group)] = "Lockdown" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 25 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                     ELSEIF [Assigned From (group)] = "FO LBS" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 17 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                      ELSEIF [Assigned From (group)] = "RTPM" THEN

                        IF {FIXED [Assigned From (group)],[Assign Date]:COUNT([e Folder Name])} > 4 THEN

                            'Exceed'

                        ELSE

                            'Not exceed'

                        END

                    END

            END

             

             

            ----------------------------------------------------------------------------------------------------------

             

            All refer the same process / needs that was requested.

             

            @Zhouyi Zhang: Any lights on this, as you may be aware of my request.

            • 3. Re: Conditional Formatting based on multiple conditions for multiple categories
              Rajhesh Kumar A S A

              @Zhouyi Zhang

               

              Requesting for support to correct my above formula, as I'm not getting the conditional formatting

              • 4. Re: Conditional Formatting based on multiple conditions for multiple categories
                Zhouyi Zhang

                Hi, Raj

                 

                Can you provide a sample workbook?

                 

                ZZ

                • 5. Re: Conditional Formatting based on multiple conditions for multiple categories
                  Rajhesh Kumar A S A

                  Zhouyi Zhang

                   

                          Kindly find the sample data attached. Also I have given the Threshold setting in the same attached workbook. As requested, when the team exceeds the threshold in weekdays / weekends, the same needs to be highlighted in red. Plz refer the formula, I used in the above thread.

                   

                  Thankz.

                  Raj

                  • 6. Re: Conditional Formatting based on multiple conditions for multiple categories
                    Rajhesh Kumar A S A

                    Zhouyi Zhang: Any updates..I have attached the sample data in the previous reply. Kindly let me know. Thanks

                    • 7. Re: Conditional Formatting based on multiple conditions for multiple categories
                      Zhouyi Zhang

                      Hi, Rajhesh

                       

                      Please find attached workbook, sorry for the delays

                       

                      Hope this helps

                       

                      ZZ