5 Replies Latest reply on Feb 12, 2019 9:29 AM by Nitya Goriparthi

# N Weeks data

Hello,

I'm using this formula to get N weeks data. But Our fiscal year stars on October 1 st, and as we changed to new year I could only go back to last 4 weeks since this Year (i.e. Year(TODAY()) has only 4 weeks but I need all weeks from start of my fiscal year.

Note: [Weeks Back] is a parameter

IF DATEPART('week',[Order Date])<    DATEPART('week', TODAY())

AND DATEPART('week',[Order Date])>= (datepart('week',today()) -([Weeks Back]))

AND YEAR([Order Date]) = YEAR(TODAY())

THEN [Order Date]

END

Thank you!

Nitya

• ###### 1. Re: N Weeks data

Hi Nitya,

In the top left corner, you can right click on you data source and select Date Properties. It will give you a window where you can set your fiscal year start to October.

Hope that helps

Yahia

• ###### 2. Re: N Weeks data

I think we can do this a little more simply. I'd start by creating a calculated field like this:

N Weeks Back Dates

// Get the date going back N number of weeks.

IF [Order Date]>=DATEADD('week', -[Weeks Back], TODAY()) THEN

[Order Date]

END

This doesn't care about the year or week number or anything like that. So it'll look something like this:

Is this what you're looking for?

• ###### 3. Re: N Weeks data

Hi, Nitya

Try this

if datetrunc('week',[Order Date]) < Datetrunc('week',Today())

and

datetrunc('week',[Order Date]) >= Dateadd('week',-[Weeks Back],Datetrunc('week',Today())) then

[Order Date]

end

ZZ

• ###### 4. Re: N Weeks data

Hi Ken,

Yes, this was what I was looking for.

Thank you!

• ###### 5. Re: N Weeks data

Thanks guys!

I used this

IF [Order Date]>= date(TODAY()-[Weeks Back]*7)

THEN 1 else 0

END