5 Replies Latest reply on Jul 12, 2016 10:37 AM by jimmy mok

    Conditional Formatting against Monthly Average by Month

    jimmy mok

      Hello Experts!

       

      I've created the crosstab below in Tableau and used the Analysis option to figure out the Monthly average phone calls for all reps.

       

      The problem is, I need to know how to format the calls for each month so that it highlights the Reps that beat the monthly call average by Month. This number will change month by month so I can't use a single target number for all months.

       

      JanFebMarchApril

      Rep 1

      5384
      Rep 23154
      Rep 37236
      Rep 45542
      Rep 52647
      Monthly Average4.43.44.85.6
        • 1. Re: Conditional Formatting against Monthly Average by Month
          Ashish Chaudhari

          Hi Jimmy,

           

          first I want to know which data source are you using? If excel then following thing will work for you. Let me know if you are using any other data source.

           

          Please find the calculated field. Also please refer to the below screenshot to validate the output.

          if SUM([Pivot field values])>= SUM({ FIXED [Pivot field names] :  SUM([Pivot field values]) / COUNT([Reps]) }) then "Green"

          ELSE "Red"

          END

           

          To get the below output you will need to do the following process which will transform your data. Please follow the steps. Once you connect the excel to tableau right click on the below fields and go to "Transform" then select Pivot Option.

          This will change the data from columns to rows as below. (It will create pivot field names and Pivot field values columns and selected columns above will disappear)

           

          Make your pills arrangements as below to get the desired output.

           

          I will upload the workbook in the next post for your reference.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Conditional Formatting against Monthly Average by Month
            Ashish Chaudhari

            Please find the attached workbook for reference of the same. Let me know if you are using any other data source.

             

            Thanks and Regards,

            Ashish Chaudhari

            • 3. Re: Conditional Formatting against Monthly Average by Month
              Mahfooj Khan

              I don't know about your data structure. If you've data like this.

              You can try something like this.

              Go through the workbook (version 9.2). Feel free to ask If you've any query.

               

              Mahfooj

              1 of 1 people found this helpful
              • 4. Re: Conditional Formatting against Monthly Average by Month
                Ashish Chaudhari

                Hi jimmy,

                 

                The kind of structure shown by Mahfooj is the standard structure. You should ideally have the data in that way. If possible try to alter the table structure as above.

                 

                The pivot feature that I have used is also doing the same thing at the tableau end but remember it works only in case of excel. Thus if you are using the any other data source other that excel please restructure the table. Try Imagining the scenario after 1 year. You will have to change the column names as Jan 2016, Feb 2016 since new year data will get added to it and every-time month data gets added you will have to alter the table for the new month which is not the standard way of doing it.

                 

                I hope this explanation helps you in your future development.

                 

                Thanks and Regards,

                Ashish Chaudhari

                • 5. Re: Conditional Formatting against Monthly Average by Month
                  jimmy mok

                  Thank you for the reply guys! I'm using TD SQL connection for my data, and not Excel, so I guess I can't use the methods prescribed here.

                   

                  I did not know that the type of connection affected how the formulas worked in Tableau. I guess you learn something new every day!