3 Replies Latest reply on Oct 26, 2018 8:07 AM by Tony R

# Multiple if Statement Help

Hi,

Need some help, as i'm brand new to Tableau. I know it's best to be able to post a workbook, but due to company security i'm not able to do so.  I have a statement that I started using that almost works, but is missing a key part.

This current calculation works for everything, but not for "Green Flat", as "Green flat" should appear when the last 3 months are blank or 0.

Current if calculation:

IF [Difference +/-] >= 1 THEN " Red Up"
ELSEIF [Difference +/-] = 0 THEN "Green Flat"
ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change"
ELSEIF [Difference +/-] <= 1 THEN "Green Down"

END

Difference +/- calculation:

IFNULL( SUM([Previous month]) - sum([Previous 3 Month Average]),0)

Previous month calculation:

ifnull

(IF

DATEDIFF('month', DATETRUNC('month', [Date]),

{MAX(DATETRUNC('month', [Date]))})=1

THEN [Number of Records]

END, 0)

Previous 3 month calculation:

ifnull( if datediff('month',[Date],today())<=3 and datediff('month',[Date],today())>=1 then [Number of Records]/3 end, 0)

Below example:

Orange bar row should show: Green Flat

Navy bar row should show: Yellow Flat, as there is data in the bar. Last 3 month average is 1 (0+2+1) / 3 = 1 and Difference +/- = 0 so it shows Green flat based on current formula, but it should be yellow flat. I can accomplish this by removing green flat from calculation, but then orange bar doesn't show Green flat.

Light blue should show yellow flat

• ###### 1. Re: Multiple if Statement Help

Tony,

I did not fully catch the gist, so my apologies if I'm way off base.

If you only want to show green flat when the previous three months

are blank or 0, then I think you will need to use a separate conditional other

than comparing zero to Difference +/- .

Not exactly sure how it would work, but one example would be,

if the Number of Records for a month will never be negative,

then you could write a separate conditional without the divide by 3:

ifnull( if datediff('month',[Date],today())<=3 and datediff('month',[Date],today())>=1 then [Number of Records] end, 0)

and then check if that sum is equal to zero.

So the overall IF/THEN would be

IF [Difference +/-] >= 1 THEN " Red Up"
ELSEIF SUM([AllBlankorZero]) = 0 THEN "Green Flat"
ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change"
ELSEIF [Difference +/-] <= -1 THEN "Green Down"

END

Also, should Green Down be for when Difference +/- is less than negative 1?

1 of 1 people found this helpful
• ###### 2. Re: Multiple if Statement Help

Hi,

I guess your IF conditions malfunctioning and quite confusing

IF [Difference +/-] >= 1 THEN " Red Up" //If this condition met then your Green Down will never appear because you've put here>=1, It means If difference is greater or equals to 1 then It will be red but again in case of Green Down you've put <=1 which is again confusing and it will be skipped from that condition.

ELSEIF [Difference +/-] = 0 THEN "Green Flat" //Once it meet the condition then it will not be part of the next ELSEIF condition

ELSEIF [Difference +/-] > -1 AND [Difference +/-] < 1 THEN "Yellow No Change" // When you're putting condition >-1 and <1, zero is also falling in that range

ELSEIF [Difference +/-] <= 1 THEN "Green Down" //Only negative values <-1 will be in Green Down

END

You can try this not sure whether it will work or not

IF

[Difference +/-] < 0 THEN "Yellow No Change"

ELSEIF [Difference +/-] = 0 THEN "Green Flat"

ELSEIF [Difference +/-] <= 1 THEN "Green Down"

ELSEIF [Difference +/-] > 1 THEN " Red Up"

END

You can create a sample mock up data which matches with structure of your original source in a packaged workbook and share with us.

Mahfooj

• ###### 3. Re: Multiple if Statement Help

Awesome, this really helped me out. Thanks for your assistance.