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

# need calculation

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

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

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

Instead of selecting 2 months select 6 weeks.

• ###### 4. Re: need calculation

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.