5 Replies Latest reply on Sep 22, 2016 9:45 AM by ravi.vankayala

    need calculation

    ravi.vankayala

      i want to see last 6 week day  data based on todays date.

       

      for example.if today is thrusday I want to view last 6 Thursdays data based on todays date. I was able to get without todays date. any idea on this ?

        • 1. Re: need calculation
          Andrew Watson

          You can use 2 filters from calculated fields:

           

          1. [DateField] > DATEADD('week',-6,TODAY()) (or a relative, green, standard date filter for the last 6 weeks)

          2. DATEPART('weekday',TODAY()) = DATEPART('weekday',[DateField])

           

          Set both to 'True' in the filters shelf if using the calculated fields technique.

           

          These can also be combined into a 1 calculated field if you prefer:

           

          [DateField] > DATEADD('week',-6,TODAY()) AND DATEPART('weekday',TODAY()) = DATEPART('weekday',[DateField])

           

          Set to True in the filters shelf.

          2 of 2 people found this helpful
          • 2. Re: need calculation
            ravi.vankayala

            thank you Andrew for your reply.

             

            I have implemented above code. I have one question. since I want to show last 6 thursdays

             

            For example If we have 4 Thursdays in Apr 2013 and 4 Thursdays in march 2013,i want to display 4 Thursdays in april and last 2 Thursdays in march so total 6 Thursdays currently I am getting 4 Thursdays in april 2013 and 4 in march.Please find attached image. since I have selected 2 months it is showing 8 Thursdays.

             

            • 3. Re: need calculation
              Andrew Watson

              Instead of selecting 2 months select 6 weeks.

              • 4. Re: need calculation
                Vikas S

                Ravi,

                 

                You can have 2 filters, 1st is to identify and limit the day of week & 2nd is to limit you data for past 6 weeks.

                 

                1. identify today's weekday & compare with your date fileld, ex Note- Order Date = YourDateField

                     DATENAME('weekday',[Order Date],'sunday')= DATENAME('weekday',TODAY() ,'sunday')

                      set your calculated field to 'True' in filter shelf.

                2 Limit your data to fetch only past 6 weeks , you can achieve this by either filtering it by weeks or by number of days.

                     [YourDateField] > DATEADD('week',-6,TODAY())

                     Set this filter to 'True'

                 

                Thanks

                Vikas

                • 5. Re: need calculation
                  ravi.vankayala

                  thanks vikas that worked