Bring back the date based on a calendar

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

Datasets

Output

Preparation

Accepted answers

jdunkerley79

Is this what you mean?

MinMaxDate.yxmd

All comments

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.

CreateCalendar.yxmd

mjtowne

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

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

Is this what you mean?

MinMaxDate.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703