2 of 2 people found this helpful
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.
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.
img.png 21.5 KB
Instead of selecting 2 months select 6 weeks.
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 that worked