Bring back the date based on a calendar

[mjtowne]

Hi,

I am trying to bring back the min and max dates based on a calendar with the parameters of date-1 and Date-365 to bring back the week numbers.

I need the min and max values to calculate automatiocally. The calendar date would contain a field with date in and a week number as an example below.

WeekNbr       Date

2                       02/01/2016                

2                       03/01/2016  

2                       04/01/2016  

2                       05/01/2016  

2                       06/01/2016  

2                       07/01/2016  

2                       08/01/2016  

3                       09/01/2016  

I am after trying to bring back the week number based on a rolling 52 week period.So if i looked today it would show the most recent week and 52 weeks back as the minweek.

Thanks

Martin

[jdunkerley79]

If I am understanding you correctly, you want to start with todays date. Convert this to a start and end (date-1, date-365) and then create a calendar.

There is a DateTimeNow tool which will give todays date.

To create the Date1 and Date365 values you can use a formula tool. I set Date365 equal to the output of the DateTimeNow tool and made Date1 equal to the date one year earlier:

DateTimeAdd(DateTimeAdd([DateTimeNow],-1, "years"), 1, "days")

Next to generate the dates I used a Generate Rows tool starting at Date1 and running to Date-365. The iteration step is a little delicate as I wanted to be a Date type:

Left(DateTimeAdd(Date,1, "days"), 10)

(The Left function truncates the return of DateTimeAdd to a Date format).

Finally last step is to create week number within the sequence. I assumed this to be defined as week 1 being first 7 days of the calendar. In which case this is given by:

Ceil((DateTimeDiff([Date],[date-1],"days") + 1)/7)

Complete sample attached. 

[mjtowne]

Hi I cant view the work book as i am using 10.0 designer 

[jdunkerley79]

Attached in 10,0 format

[mjtowne]

Sorry answer to your question is i already have the calendar table as my data set. What i want to find out is the min and max date based on a 52 week rolling period or if i want to change it slightly ie

the date date-7 to date-365.

Thanks

Martin

[jdunkerley79 · Accepted solution]

Is this what you mean?