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

    How do I create a formula that shifts order dates

    Henry Stass

      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:

       

      Screenshot 2016-06-21 09.34.02.png

       

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

       

      What I have created:

       

      Screenshot 2016-06-21 10.13.13.png

       

      Can any body help??

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

          dateadd('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.

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

            Hi 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

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

              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
                Mark Fraser

                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).

                Logical Functions

                and

                https://www.interworks.com/blog/tmcconnell/2015/02/10/case-statements-vs-if-statements-tableau

                 

                The other function I used was DATEDIFF()

                Date Functions

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

                  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
                    khalid norat

                    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
                      Mark Fraser

                      dateadd('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.

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

                        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
                          Henry Stass

                          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