13 Replies Latest reply on Sep 25, 2018 12:45 AM by James Bills

# How can you filter calculated time periods?

I view my arrival time data in decades.

These are created by the following calculated field:

IF DAY([ETA]) < 11 THEN "1D"

ELSEIF DAY([ETA]) < 21 THEN "2D"

ELSE "3D"

END

I want to be able to filter to show just the last 2 decades, current decade and the next 3 decades.

So as of 24/9/18, I would want to show 1D Sep, 2D Sep, 3D Sep, 1D Oct, 2D Oct, 3D Oct

Can't seem to figure out how to do this. The communities help would be much appreciated!

Thanks,

James

• ###### 1. Re: How can you filter calculated time periods?

Hi

place it on the filter shelf and set to true

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: How can you filter calculated time periods?

I think there are several ways you can approach this.

In the attached I made a FIXED LOD calc that sets a 1 on the mark that is the decade that TODAY() falls under.  Using that alone you would set a table calc to grab the 2 before it and the three after it.

But if you expanded your Decade logic to create a more comprehensive calc that includes the year and month in the decade value, then you would only have to use the same [Decade today] calc and with unique values along the whole range you could grab the 6 data points with a standard calc.

I would make the decade calc to be in the format YYYYMMD format.  This would sort numerically in calendar order and looking forward and backward would be easier.

• ###### 3. Re: How can you filter calculated time periods?

Jim -- You can't just go by month.  If James had asked this question on Sept 3, we would need to get 2 decades from August, three from Sept, and one from Oct.

• ###### 4. Re: How can you filter calculated time periods?

Hi Jim. As Joe put in his comment, I can not just filter by month or by day. The value would change depending on which decade I'm in, or how long through it.

• ###### 5. Re: How can you filter calculated time periods?

Good point Joe - Hope all is well - see you in NOLA

• ###### 6. Re: How can you filter calculated time periods?

James -- Have you had a chance to look at what I did in my uploaded sample workbook?

• ###### 7. Re: How can you filter calculated time periods?

Hi Joe.

Thanks for the calc although how would I use it in a table calc to get show the decades i wanted? This is was definitely the logic i was thinking of using.

I've been having a look but I'm sorry I don't understand the other part of your solution.

• ###### 8. Re: How can you filter calculated time periods?

In the attached I changed my Decade Today calc to a measure.  Next I made a calc called index.  It's on the text shelf.  Notice the table calc settings I put on it.  I want to get a 1-through-N value along from the start of the data to the end.

Go to the next copy of the sheet.

I made a calc called Anchor Index.  It loads the value of index where Decade Today is set onto all marks. (Today that value is 8.)  Again, the table calc settings match what worked correctly for index.  (WINDOW_MAX puts the value on all columns.  Without that, it would just show up on the one column where [Decade Today] is set.)

Finally I created [Decades to grab].  It sets 1 or 0.  That's also displayed on text.

Now you can use that as a filter and just display the 6 marks you are concerned about.

1 of 1 people found this helpful
• ###### 9. Re: How can you filter calculated time periods?

Sorry Joe. I can't seem to get into this packaged workbook. (didn't have a problem with the first one)

• ###### 10. Re: How can you filter calculated time periods?

Try again with the attached.

1 of 1 people found this helpful
• ###### 11. Re: How can you filter calculated time periods?

Great.Thanks that's worked. Really neat solution

• ###### 12. Re: How can you filter calculated time periods?

So it's really interesting that you couldn't open the prior workbook.

I'm on 2018.3 Beta.  I'm still trying to figure out the compatibility relationships among 2018.1 through 2018.3.  When I open a workbook from a lower version, I get an upgrade message for versions 10.5 or older, but not for any of the 2018 versions.  When I don't get an upgrade message from workbooks I open from here, I had been saving them as 2018.1 version.  Guaranteed that no matter what 2018 you're on, you'll be able to open it.

Last week I asked someone who turned out to be on 2018.2 if he could open my 2018.3 version, and he could.  I took that fact, combined with Tableau's absence of upgrade messages for 2018 versions as a sign that any 2018.x version can open 2018.3.

So the prior workbook that you couldn't open was 2018.3.

What version are you on?  Maybe 2018.2 can open 2018.3, but 2018.1 cannot.

• ###### 13. Re: How can you filter calculated time periods?

I'm on 2018.1 at the moment Joe Oppelt. I haven't had any problems so far but will be upgrading soon