Skip to main content
Question

Converting dates into weekly cycles

  • December 1, 2017
  • 6 replies
  • 311 views

Forum|alt.badge.img

Hi, I have a bunch of data in SQL with date stamps and I have had a request to convert the data into our 4 weekly cycles. eg. any dates from 7th Jan to 13th Jan 2017 are a week 1, from 14th Jan to 20th Jan 2017 are Week2 ... Week 3, Week 4 and then 4th Feb to 10th Feb being Week 1 again ongoing. The data continues to be added to and they want me to be able to continue to run this workspace into the future and keep working out on the new data which week is it falling into. Any thoughts

6 replies

takashi
Influencer
  • December 1, 2017

Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.

However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. .


Forum|alt.badge.img
  • Author
  • December 1, 2017
takashi wrote:

Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.

However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. .

Yeah is a little more complicated as our week does commence on a Saturday and also it isn't limited to the year as it has been continuing for years and going into the future. So when I look forward Saturday 30th December is a Week 1 and Saturday 6th Jan is Week 2. Not sure if I can feed it a date and say this is Week 1 and then calculate from that point? Thanks for the speedy response and thoughts.

 

 

 


takashi
Influencer
  • December 1, 2017
takashi wrote:

Hi @garryp, you can convert a date YYYYmmdd to Sunday or Monday-based week of year (00 - 53) with the DateTimeConvertor (Output Format: %U or %W). I think you can calculate desired 4 weekly cycle values (iteration for 1 to 4) based on these values.

0684Q00000ArL2QQAV.png

However, 2017-01-07 was Saturday. Since the week of year is Sunday or Monday-based, the operations could be a little complicated if required 4 weekly cycle should start with Saturday. . 

Another (simpler) thought. If the start date was given (e.g. 2017-01-07), the 4 weekly cycle number of any future date can be calculated based on the number of elapsed days from the start date.

 

@int(<elapsed days> / 7) % 4 + 1
Test workflow:

 

0684Q00000ArMYuQAN.png

If a future date were given, the number of elapsed days since the start date can be calculated with the DateTimeCalculator (Mode: Calculate Interval between Datetime, Result Type: Days).

ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • December 1, 2017
SELECT a."Date"date(a."Date",'start of month','weekday 6'as first_saturday from "Output" a

The inlinequerier can give you the first Saturday of the month for a given date and you can work out the week number based on the difference between these two dates.

Edit: looking at your comments though, if a week does not start consistently on the first saturday of the month this probably isn't going to work. What is the logic that makes 30th December 2017 Week 1 of January?


Forum|alt.badge.img
  • Author
  • December 5, 2017
ebygomm wrote:
SELECT a."Date"date(a."Date",'start of month','weekday 6'as first_saturday from "Output" a

The inlinequerier can give you the first Saturday of the month for a given date and you can work out the week number based on the difference between these two dates.

Edit: looking at your comments though, if a week does not start consistently on the first saturday of the month this probably isn't going to work. What is the logic that makes 30th December 2017 Week 1 of January?

 

Hi, the weeks are not linked to years, they are an ongoing 4 week cycle which started years ago. 

 

 

I have made a work around with a table with all the dates and what week they fall into and just look up in that to find the week that the job has fallen into. 

 

 

Thanks for your assistance

 


Forum|alt.badge.img
  • December 5, 2017

Hi @garryp

with FME 2017 DateTimeCalculator you can find how many days there are between your date and the beginning of the four-week cycle date. With this value, you can calculate the date week number as:

@floor((@Value(_days)-@floor(@Value(_days)/28)*28)/7)+1

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings