9 Replies Latest reply on Jun 21, 2016 12:58 PM by Henry Stass

# How do I create a formula that shifts order dates

Hi,

I am trying to re-create the following worksheet, but I am having trouble creating a formula the shifts the order dates.

This is what it should look like:

I have created the following but as i said I am stuck with the formula to complete it.

What I have created:

Can any body help??

• ###### 1. Re: How do I create a formula that shifts order dates

This will give you the date 48 months prior too todays date.

Once you have this you can do <= [orderdate] to give you a filter field to only show the last 48 months.

If you have further questions or need help implementing please let me know.

1 of 1 people found this helpful
• ###### 2. Re: How do I create a formula that shifts order dates

Hi Khalid,

As you suggested I created a calculated filed. I am pretty new to Tableau so it is not always easy to navigate.

I don't quite understand what you mean with the "Once you have this you can do <= [orderdate] to give you a filter field to only show the last 48 months." I am guessing that you want me to create a filter, or is a filter automatically created if I trigger a certain action?

Henry

1 of 1 people found this helpful
• ###### 3. Re: How do I create a formula that shifts order dates

Hi Henry

I'll try and walk you through it...

We need 2 dates, a start date and an end date.

For the start date, we use Order Date

For the end date, both Khalid and I are using TODAY() - you wanted currently month.

Then we need to test whether the order date is within 48 months, we use the datediff function, and we tell it we want to use months.

Based on the above, You get this calculated field

IF DATEDIFF('month',[Order Date],TODAY()) <49 THEN 'SHOW' ELSE 'HIDE' END

In English

If the number of months between the order date and today is less than 49 (i.e. between 1-48) then return SHOW

if the number of months aren't in the 1-48 month window, return HIDE

Finally, you place this new field in the filter shelf, and set it to TRUE, you only want records which are within the 48 month window.

Screenshot below showing everything I have mentioned.

I know you're very new to Tableau, but does that help?

Cheers

Mark

3 of 3 people found this helpful
• ###### 4. Re: How do I create a formula that shifts order dates

Note - although my syntax/ methodology is slightly different from Khalid's, we are effectively saying the same thing.

I have split my function out for ease of explanation.

If you plan on going further with Tableau (or nearly any software/ programming) you would be very well advised to really understand logic statements (IF and IIF functions, also CASE).

and

The other function I used was DATEDIFF()

1 of 1 people found this helpful
• ###### 5. Re: How do I create a formula that shifts order dates

I think based on what Khalid says I did the following if it helps.

Right click in the dimensions field and select Create Calculated Field call it 48 months or something and enter the formula shown by Khalid dateadd('month',-48,today())

This creates you a moving point in time based on today's date.

Then create another calculation say OrderDate 48 and enter the formula [Order Date]>=[48 Month Order Date]

Then drag the Order Date 48 pill to your Filters section and Select True as the filter.

Or you could combine the two parts into one formula e.g. Create calculated field Order Date 48 and enter the formula as [Order Date]>=DATEADD('month',-48,Today()) and drag this to the filter area and select True

Worked for me and I hope it helps

2 of 2 people found this helpful
• ###### 6. Re: How do I create a formula that shifts order dates

Hi Henry,

If you are having difficulty with this please share a sample workbook

either with your data or superstore data and I'm sure myself or one of the guys above can help you achieve the results you require and understand the process a little better.

2 of 2 people found this helpful
• ###### 7. Re: How do I create a formula that shifts order dates

Thanks Khalid.

I always use DATEDIFF(), I never thought of using DATEADD() with a minus!

I don't know if there are any performance or other differences, but a useful trick to know!

@Henry, either method DATEDIFF() or DATEADD() (as described in this thread) will get you what you need.

2 of 2 people found this helpful
• ###### 8. Re: How do I create a formula that shifts order dates

You need to create a second calculated field

Calculatedfield 2  ---->   calculatedfield1 <= orderdate

this will give you a true false Boolean field

use this field as a filter and show only true values

that's the values that fit the criteria of being within 48 months

Hope its a little clearer

2 of 2 people found this helpful
• ###### 9. Re: How do I create a formula that shifts order dates

Hi every one,

and thanks for all you support, is absolutely amazing :-)

I have added the calculation and it seems to be working. But I am still missing something, the present date should be June 2016 - 48 months. I have attached the challenge that I received including the my result so far.

1 of 1 people found this helpful