5 Replies Latest reply on May 2, 2018 8:23 AM by Marc-Anthony Di Biase

    KPI Symbol excluded when current month is blank

    Teresa Wright

      I am a Tableau 10.4 desktop user and need to display a KPI symbol when a variance of <>150% exists between the current month and the prior 3 month average.  My problem occurs when the current month is blank or equal to zero.

       

      My Exception/KPI calculation

      screen shot 1.jpg

       

      Most of the time this works fine as in the screen shot below.

       

      screen shot 2.jpg

       

      However if the current month happens to be blank, I am not getting the "check mark" symbol to display.

       

      Dashboard 3.jpg

       

      Does anyone have suggestions on how I could make this work?  I have also attached a mock up file.

        • 1. Re: KPI Symbol excluded when current month is blank
          Marc-Anthony Di Biase

          If you edit your calculated field to the following;

           

          IF [Expense-Income Variance %]>1.5 OR [Expense-Income Variance %]<-1.5 OR ISNULL([Expense-Income Variance %]) THEN '✔' ELSE ' ' END

           

          Would this satisfy your requirement?

           

          You won't be able to work around the Exp Current Month because even if you pick it up as '0' instead of 'Null', you're doing a division operation which will give you a Null if you try dividing by zero.

           

          Let me know if I'm missing something here.

          • 2. Re: KPI Symbol excluded when current month is blank
            Teresa Wright

            Hi Marc-Anthony.  Thanks for responding to my question.  I marked it correct, however I am not quite getting the effect I would like, and I thought maybe you would have a suggestion. 

             

            In this screen shot, there are three data rows that I would not want to display the KPI, as the current month and prior 3 mo average are = $0.

            screen shot 1.jpg

             

            Perhaps my KPI calcuation is not the issue here?

             

            screen shot 3.jpg

             

            Could it be my variance calculations are the problem?

             

            screen shot 1.jpg

             

            screen shot 2.jpg

             

            Thanks for any suggestions you have have.  If needed I can repost this in a new discussion.

            • 3. Re: KPI Symbol excluded when current month is blank
              Marc-Anthony Di Biase

              Hi Teresa,

               

              Based on the info provided I think this is what you're looking for - basically I'm checking if there is an amount in the prior month expenses that exists, then moving towards the logic checking based on the values and variance. Please see the output below;

               

              2018-05-02_9-54-55.png

               

              Here's the calc field for your reference;

               

              IF(ATTR([Exp Current Month])>0 OR

                      ATTR([Exp Prior Mo])>0 OR

                      ATTR([Exp Prior Mo 2])>0 OR

                      ATTR([Exp Prior Mo 3])>0)

              THEN

                  IF [Expense-Income Variance %]>1.5 OR

                          [Expense-Income Variance %]<-1.5 OR

                           ISNULL([Expense-Income Variance %])

                  THEN '✔'

                  ELSE ' '

                  END

              ELSE ' '

              END

              • 4. Re: KPI Symbol excluded when current month is blank
                Teresa Wright

                Thanks very much!  The data is displaying the way I want it.  I am assuming there is no way to make the headers or the KPI symbol display in red as well?  Might be asking too much to do that.

                 

                screen shot 1.jpg

                 

                Regards,

                Teresa

                • 5. Re: KPI Symbol excluded when current month is blank
                  Marc-Anthony Di Biase

                  Since you're not using another symbol for the ones that don't hit your criteria, you could just change the test color of the symbol? Right click the checkmark -> format -> change color of default text.

                   

                  If you were using another symbol, you probably would run into some difficulty trying to conditionally format the column - crosstabs are always very tricky to use in Tableau as you want to use them like an excel based tool, however you must remember Tableau is meant for data visualization not spreadsheets.