7 Replies Latest reply on Feb 17, 2017 7:13 AM by sibtain fattah

    How can I add color measure inside a tooltip?

    sibtain fattah

      Currently, I have a tooltip that displays the following:

       

      Year Month (Month / Year): <Year Month (Month / Year)>

      To Date (current fiscal): <SUM(To Date (current fiscal))>

      Plan (fiscal year plan): <SUM(Plan (fiscal year plan))>

      Forecast(till end of year): <SUM(Forecast(till end of year))>

      Plan-Forecast Variance(%): <AGG(Plan-Forecast Variance(%))>

       

      I'm trying to create a dynamic measure for the plan-forecast variance so say  when I hover over a mark in the worksheet it displays a red color for negative values in the tooltips box and  displays a green color for positive values in the tooltips.

        • 1. Re: How can I add color measure inside a tooltip?
          David Li

          Hi Sibtain, and welcome to the forums!

           

          In order to use conditional formatting in tooltips (as of 10.1), you'll basically need to create multiple fields or inline calculations, one for each of the formats. Then, you just put these next to each other in the tooltip text editor.

           

          For instance, for Plan, you'll create one field that only shows positive numbers (and zero) and another field that only shows negative numbers. Then, put these next to each other in the tooltip editor, format one green, and format the other red.

          2 of 2 people found this helpful
          • 2. Re: How can I add color measure inside a tooltip?
            sibtain fattah

            Hi David and thank you!

             

            I get the gist of what you are saying and it makes sense to me conceptually however how would I write this in a calculated field? I'm relatively new to Tableau so I'm still trying to get grasp of this.

             

            I gave it my own try and wrote this however It's very incorrect:

             

            > I created 2 fields:

            • PositivePlanVariance: IF [Plan-Forecast Variance(%)] > 0 THEN  "Positive"  END

             

            • NegativePlanVariance: IF [Plan-Forecast Variance(%)] < 0 THEN  "Negative"  END

             

            >Then I put in the tooltip editor:

            Plan-Forecast Variance(%): <AGG(Plan-Forecast Variance(%))> <PositivePlanVariance> <NegativePlanVariance>

            • 3. Re: How can I add color measure inside a tooltip?
              David Li

              No problem! It's quite simple and you can do it either inline (by double-clicking in the Marks card) or by creating a calculated field. For instance, for the positive:

              IF SUM([Plan (fiscal year plan)]) >= 0 THEN SUM([Plan (fiscal year plan)])

              END

              If Plan is positive or zero, the value will be shown. Otherwise, it will be null (since there's no condition that applies).

               

              You can also use IIF(), which is basically a function for an IF-THEN statement.

              IIF(SUM([Plan (fiscal year plan)])>=0, SUM([Plan (fiscal year plan)]), NULL)

              And for the other field, which will cover the negatives, just change the equality operator.

              IIF(SUM([Plan (fiscal year plan)])<0, SUM([Plan (fiscal year plan)]), NULL)

              1 of 1 people found this helpful
              • 4. Re: How can I add color measure inside a tooltip?
                David Li

                In your version, you need to put the aggregation function--SUM()--around the field. And after THEN, you need to put the field in again, not "Positive" and "Negative". What you want Tableau to do is use one field to display all the positive numbers (and format this green) and use another field for all the negative numbers (and format that red). Only one of these two fields will be visible at a time because when one has a value, the other is null.

                • 5. Re: How can I add color measure inside a tooltip?
                  sibtain fattah

                  I tried both ways, yours and mine however the SUM function won't work because says the argument to SUM is already an aggregation and can not be aggregated further.

                   

                  Your version:

                  IIF(SUM([Plan-Forecast Variance(%)])>=0, SUM([Plan-Forecast Variance(%)]), NULL)

                   

                  This is the calculation I have for Plan-Forecast Variance(%):

                  (SUM([Plan (fiscal year plan)])-SUM([Forecast (to end of fiscal year)]))/SUM([Plan (fiscal year plan)])

                   

                  It makes sense to me that it can't be aggregated further. any other alternatives you could suggest?

                  • 6. Re: How can I add color measure inside a tooltip?
                    David Li

                    Ah, I see. Try this:

                     

                    • PositivePlanVariance: IF [Plan-Forecast Variance(%)] >= 0 THEN  [Plan-Forecast Variance(%)]  END
                    • NegativePlanVariance: IF [Plan-Forecast Variance(%)] < 0 THEN [Plan-Forecast Variance(%)]  END

                     

                    >put in the tooltip editor:

                    Plan-Forecast Variance(%): <PositivePlanVariance> <NegativePlanVariance>

                    1 of 1 people found this helpful
                    • 7. Re: How can I add color measure inside a tooltip?
                      sibtain fattah

                      Figured it out. The formula you suggested is perfect.

                       

                      The thing that made it work was adding AGG:

                      Plan-Forecast Variance: <AGG(PositivePlanVariance)> <AGG(NegativePlanVariance)>

                       

                      Also, this post helped as well:

                      Changing Tooltip Colors Based on Conditions

                       

                      Thanks for your help David!