5 Replies Latest reply on Feb 13, 2018 10:43 AM by Joe Oppelt

# If Statement for Date

Hi guys,

I need some help with a calculated field.

The gray and the blue line in the attached file represent plan and actual year-over-year growth.

I'm trying to come up with a formula to control the blue line as it is misrepresenting what's actually happening.

Basically, I would like it to not show 2019 data at all.

Since the two graphs share a the same date field, I believe this will need to be done using an IF Statement.

Thank you!

• ###### 1. Re: If Statement for Date

But you want to create your own table calc instead of using tableau's quick table calc for the % Diff you need.  I dragged the green SUM(Actuals) pill from the ROWS shelf into an empty calc, and it created this for us:

(ZN(SUM([Actuals])) - LOOKUP(ZN(SUM([Actuals])), -1)) / ABS(LOOKUP(ZN(SUM([Actuals])), -1))

If you wanted to write your own percent diff calc, that's what it would look like.

Modify that to result in NULL where you want the line to stop.  Replace the quick table calc on your sheet with the new calc, and you should get the behavior you want.

• ###### 2. Re: If Statement for Date

And the modification would look like this:

IF ATTR(YEAR) < 2019 then (ZN(SUM([Actuals])) - LOOKUP(ZN(SUM([Actuals])), -1)) / ABS(LOOKUP(ZN(SUM([Actuals])), -1)) END

So because there is no ELSE portion of this IF statement, tableau will load nulls for 2019 and beyond.

• ###### 3. Re: If Statement for Date

Hi,

Thank you so much for your help!

Sorry about the file issue. I just replaced it.

I can't filter out nulls because it seems to affect the projected values as well.

I tried your formula and it's not working for some reason.

Thanks

• ###### 4. Re: If Statement for Date

IF ATTR(YEAR) < 2019 then (ZN(SUM([Actuals])) - LOOKUP(ZN(SUM([Actuals])), -1)) / ABS(LOOKUP(ZN(SUM([Actuals])), -1)) END

Works! Thanks so much!

1 of 1 people found this helpful
• ###### 5. Re: If Statement for Date

Sere attached.