7 Replies Latest reply on Feb 16, 2017 12:54 PM by venky v

    Last 8 week days

    venky v

      Hello,

       

      Can anyone help me with calculation to find last 8 weekday? for example (last 8 monday's).

       

      Thank you.

        • 1. Re: Last 8 week days
          Joe Oppelt

          I would make a calc that looks something like this:

           

          IF [Date] >= DATEADD('day', -56, [date you want to start from])

          AND

          DATENAME('day',[Date]) = "Monday"

          then 1 else 0 END

           

          Put that on your filter shelf and select for value = 1.

           

          DATEADD adds (or in this case subtracts) x-many units (in this case, days) from the target date.  So going back 56 days gets a span of 7 weeks.

           

          DATENAME returns the text value of the date part you specified.  So only get those days that are Monday.

           

          Syntax might need tweaking.   I just typed that off the top of my head.

          • 2. Re: Last 8 week days
            Deepak Rai

            Hi,

            You can use a similar approach . I am attaching superstore sample workbook which I just used to help someone to find latest date. Instead of latest you can use your date only after selecting 8 week data through filter.

            • I used FIXED LOD
            • {FIXED [Customer Name]:Datename('weekday',([Order Date (copy)]))}

             

            I was using this in attached workbook.

            {FIXED [Customer Name]:Datename('weekday',MAX([Order Date (copy)]))}

            Hope it Helps!!!

            Thanks

            Deepak

            • 3. Re: Last 8 week days
              venky v

              Hi Joe,

               

              I tried your formula but it shows zero for each row. below is the calculation I used.

               

              IF [CAL_DT] >= DATEADD('day', -56, TODAY())

              AND

              DATENAME('day',[CAL_DT]) = "Monday"

              then 1 else 0 END

              • 4. Re: Last 8 week days
                venky v

                Hi Deepak,

                 

                It gives me latest weekday for each row but I want data to be displayed only for last weekdays. Hope Iam clear.

                • 5. Re: Last 8 week days
                  Joe Oppelt

                  That should work.  So there is something more going on in there, and I would need a packaged workbook and your tableau version to look at it further.

                  • 6. Re: Last 8 week days
                    Deepak Rai

                    Venky,

                    you need to just use my LOD for finding name of Day in your last 8 Weeks data. Then also bring that LOD to filter and select only Monday. It will work. Just use this calculation over your 8 Week Dates data.

                    {FIXED :Datename('weekday',([Order Date (copy)]))}

                    Thanks

                    Deepak

                    • 7. Re: Last 8 week days
                      venky v

                      Joe,

                       

                      It did work. I replaced 'day' with 'weekday' in DATENAME('day', [date])='monday'.

                      Thanks for the help.