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

# KPI Symbol excluded when current month is blank

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

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

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

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

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

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

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.

Perhaps my KPI calcuation is not the issue here?

Could it be my variance calculations are the problem?

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

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;

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

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.

Regards,

Teresa

• ###### 5. Re: KPI Symbol excluded when current month is blank

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.