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

    Break Dates by Custom Weeks

    Numerouno A

      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"

       

       

      Appreciate your help!!

       

      Thanks,

      NU

        • 1. Re: Break Dates by Custom Weeks
          Shinichiro Murakami

          Hi Numerouno

           

          I did maybe too much over-complicated but anyways,

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Break Dates by Custom Weeks
            Jim Van Sistine

            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)

            //add 4 to get values 1-4 instead of -3 to 0

            +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
              arvindgarg

              Below calculation should work.

               

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

               

              • 4. Re: Break Dates by Custom Weeks
                Numerouno A

                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
                  Jim Van Sistine

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

                   

                  • 6. Re: Break Dates by Custom Weeks
                    Shinichiro Murakami

                    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
                      Bryce Larsen

                      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
                        Numerouno A

                        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
                          Bryce Larsen

                          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.