4 Replies Latest reply on Jun 18, 2013 1:08 PM by Matt Lutton

Best practice for a calculated field based on a dynmaic date?

I am in the process of creating some reporting that needs to look at a date window of today through today minus 24 months. I need to perform a custom calculation for each of the 24 time periods, but I am not sure how to pass that date\variable to a calculated field.

I am new to Tableau, and assume there must be some way to accomplish this... help!

Any help pointing me in the right direction would be greatly appreciated, thanks in advance!

E

• 1. Re: Best practice for a calculated field based on a dynmaic date?

Welcome to the forums Eric. Tableau doesn't use variables per se, instead we use formulas in calculated fields to create stuff. The formula for what is sounds like you're wanting is:

DATEADD('day', -1, TODAY() )

That will return yesterdays date.

--Shawn

• 2. Re: Best practice for a calculated field based on a dynmaic date?

Understood.

My challenge is that I need to produce a measure for each of the past 24 months (i.e. each period needs to be calculated based on a column date).

I am not cerrtain how to build a single calculated field where datediff('month) <= Today and datediff('month) >= 24 will pull dynamic data...

I guess I could setup 24 separate calc fields?

• 3. Re: Best practice for a calculated field based on a dynmaic date?

Eric it really doesn't sound like you're using Tableau to the best advantage. Could you post a bit of sample data, or better yet a packaged (twbx) workbook that shows what you're attempting to create, as close as you can. Or you could use the Superstore dataset to create something.

--Shawn

• 4. Re: Best practice for a calculated field based on a dynmaic date?

You can use the [date] field in a calculation, just like any other field.  You should be able to write one calculation to handle all possible time periods, I'd think.

We can't really know without seeing your actual workbook, or some sample data.