9 Replies Latest reply on Jun 19, 2018 4:48 AM by Bryce Larsen

# Break Dates by Custom Weeks

How do i break dates by groups ??

for Example  in Data set from April 25 to May 22

i want the dates Broken by weeks of 7 days  from May 22

May 22- May 16 "Week 4"

May 15- May 9 "Week 3"

May 8- May 2 "Week 2"

May 1- April 25 "Week 1"

Thanks,

NU

• ###### 1. Re: Break Dates by Custom Weeks

Hi Numerouno

I did maybe too much over-complicated but anyways,

Thanks,

Shin

• ###### 2. Re: Break Dates by Custom Weeks

Numerouno,

Try the following calculations.

LastDate

{max([Date])}

Week#

//Ceiling command truncates decimals, so -3.8 and -3.1 both become -3   (INT will also work here)

CEILING(

//Use days/7 instead of weeks to account for mid-week starting days

DATEDIFF('day',[LastDate],[Date])/7)

+4

I was able to create this view using those 2 formulas.

My workbook is attached.  Hope this helps!

- Jim

• ###### 3. Re: Break Dates by Custom Weeks

Below calculation should work.

'WEEK ' + STR(FLOOR((DATEDIFF('day',{min([Date])},[Date])) /7) +1)

• ###### 4. Re: Break Dates by Custom Weeks

How do i create a calculated field that shows  range  next to week

Example if  i am looking at week 1 it should show the start date and end date

is that possible???

• ###### 5. Re: Break Dates by Custom Weeks

Sure.  Just find the min and max date per week.

• ###### 6. Re: Break Dates by Custom Weeks

HI Numerouno

You can customize the formula, but just as one example.

If this helps., could you mark my answer as correct to close the thread not from inbox view but from original post.

Thanks,

Shin

• ###### 7. Re: Break Dates by Custom Weeks

Hello! Some great methods above. I just wanted to try another methodology and avoid using LOD expressions.

Often times we want to store the Week Start or the Week End of the field so we can use in Date Filters. As such, that's what I wanted to try to do first.

Steps for Week Start:

• CF_Date_Min - identify min date in the data: {MIN([Date])}
• CF_WeekdayStart - determine the day of the week: DATEPART("weekday", [CF_Date_Min]
• Truncate the [Date] field to the start of the week, and then depending on if the Weekday precedes the Weekday of the CF_Date_Min, add or subtract days:

Week End:

• add 6 days to CF_Date_WeekStart - simple enough! DATE(DATEADD("day", 6, [CF_Date_WeekStart]))

Now with CF_Date_WeekStart we can find the week number:

Lastly, you can make the label you wanted:

Pretty straightforward and produces the desired result:

Hope one of the posts have helped you reach your goal!

• ###### 8. Re: Break Dates by Custom Weeks

Thanks!! Larsen .]This is really helpful.

One last question.. How do i manually enable the dates if there is no data for a station .

Example : if i am looking at a station for last 28 days from  5/21/2018 it should be from  04/22/2018 to 05/21/2018 and respective 4 weeks are

04/23/2018/- 04/29/2018- week1

04/30/2018-05/06/2018- week 2

05/07/2018-05/13/2018- week 3

05/14/2018-05/20/2018-week 4

what if there is data for that station in  third week  till 05/12/2018

How do i force the dates to be from 05/07/2018-05/13/2018???

• ###### 9. Re: Break Dates by Custom Weeks

Do you mean how to show data for dates that don’t exist for a given station? Easiest thing is to joon a date file prior to bringing in.

Otherwise, you could make your calculations be something like:

SUM(IF [Station]=[Parameter Station] THEN [Visits] ELSE 0 END)

Where the station you care about is a parameter and you’re tracking the number of visits. This allows you to show dates for all dates in the dataset even if it doesn’t exist for the one of interest.

Alternatively, maybe you can fix the X axis to include all dates and/or use a hidden reference line that is {MAX([Date])}. I haven’t tested yet, but want to provide a few options quickly.