9 Replies Latest reply on Feb 13, 2018 9:04 AM by Baptiste Colmagro

# Exclude a specific year without using filter

Hi Folks,

I would like to exclude a specific year of my profit:2018, but I don't want to use the filter.

Why?

My goal is to create an envelope chart with a minimum and a maximum for each week, but I don t want 2018 to be part of this min/max.

I was thinking of using an LOD such as Exclude but I can t find the right calculation..

• ###### 1. Re: Exclude a specific year without using filter

Baptiste,

create a calculated field:

Non-2018 Date

IF YEAR([Date])!=2018 THEN [Date]

END

Use this instead of "Date" dimension.

Hope it helps.

Michael Ye

1 of 1 people found this helpful
• ###### 2. Re: Exclude a specific year without using filter

This should work too

IF Year(Date)<2018 THEN Date END

Drag it to Columns or Rows Wherever you need it.

• ###### 3. Re: Exclude a specific year without using filter

Hi Michael,

Exactly this is what i thought of providing, but felt that is it a right solution.

Thank you, now i am gaining some confidence

• ###### 4. Re: Exclude a specific year without using filter

Vamsi,

Try, then you will get it. The only way to master Tableau fluently is practice, practice and practice.

Michael Ye

• ###### 5. Re: Exclude a specific year without using filter

Hi Michael,

Thank you for your help, but this is not exactly what I m looking for. I want to exclude 2018 in my measures variables, not in my Date dimension.

My goal is to do a dual axis with one of the axis with 2018, and the other one without 2018. I want to study the seasonality of my weekly profit...

Am I clear?

So far I have used your calculation idea and I have this calculation:

If ATTR(YEAR([Load Date])!=2018) then

MAX({INCLUDE DATEPART('year', [Load Date]):SUM([B/D Week])} )

END

But it does not really work because it doesn t show the first 7 weeks (I guess because we have already spent 7 weeks in 2018)

• ###### 6. Re: Exclude a specific year without using filter

Hi Baptiste,

I would suggest you to prepare two separate workbooks(axis with 2018, and the other one without 2018) and place them in dashboard for comparison. I think with Michael solution this is easily achievable.

• ###### 7. Re: Exclude a specific year without using filter

All the interest of this visualisation is to be on the same sheet. It's harder but I'm sure there is a way to do it. Harder to find out the right one ....!

• ###### 8. Re: Exclude a specific year without using filter

Baptiste,

For the measure without a year,build a calculated filed. Here I use sales as measure and without year 2016

IF YEAR([Order Date])!=2016 THEN [Sales]

END

You can see there is a blank in the non 2016 sales lines.

Michael Ye

• ###### 9. Re: Exclude a specific year without using filter

Hi Michael,

I work with Seasonality, and with your formula, I don t sea all the results anymore, the maximum I try to calculate with the 4 other years between week 1 and 7 doesn't appear.