-
1. Re: How do I create a formula that shifts order dates
khalid norat Jun 21, 2016 5:18 AM (in response to Henry Stass)1 of 1 people found this helpfuldateadd('month',-48,today())
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.
-
2. Re: How do I create a formula that shifts order dates
Henry Stass Jun 21, 2016 6:08 AM (in response to khalid norat)1 of 1 people found this helpfulHi Khalid,
Thank for your help.
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
-
3. Re: How do I create a formula that shifts order dates
Mark FraserJun 21, 2016 6:16 AM (in response to Henry Stass)
3 of 3 people found this helpfulHi 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
-
4. Re: How do I create a formula that shifts order dates
Mark FraserJun 21, 2016 6:22 AM (in response to Mark Fraser)
1 of 1 people found this helpfulNote - 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
https://www.interworks.com/blog/tmcconnell/2015/02/10/case-statements-vs-if-statements-tableau
The other function I used was DATEDIFF()
-
5. Re: How do I create a formula that shifts order dates
david Rowley Jun 21, 2016 6:42 AM (in response to Henry Stass)2 of 2 people found this helpfulI 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
-
6. Re: How do I create a formula that shifts order dates
khalid norat Jun 21, 2016 7:22 AM (in response to Henry Stass)2 of 2 people found this helpfulHi 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.
-
7. Re: How do I create a formula that shifts order dates
Mark FraserJun 21, 2016 7:25 AM (in response to khalid norat)
2 of 2 people found this helpfuldateadd('month',-48,today())
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.
-
8. Re: How do I create a formula that shifts order dates
khalid norat Jun 21, 2016 7:25 AM (in response to Henry Stass)2 of 2 people found this helpfulYou 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
-
9. Re: How do I create a formula that shifts order dates
Henry Stass Jun 21, 2016 12:58 PM (in response to khalid norat)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.
-
Need Help.twbx 474.7 KB
-