8 Replies Latest reply on Jul 24, 2018 7:17 AM by Shinichiro Murakami

    How to create Week based bins?

    Abhilash Sharma

      Hi all,


      I have a question regarding bucketing of WEEK values based on user selection.



      I need a parameter which has values as 1 WEEK, 4 WEEK, 6WEEK and 8 WEEK. The user can choose to see data at any of these levels based on the Start date he selects.



      Start date = WEEKS ENDING 10/21/17

      End date = WEEKS ENDING 01/27/18

      Total number of weeks between start date and end date = 15


      Parameter selection: 1 week

      This will mean the graph should show weekly data for all weeks between start date and end date. This is very simple as data is already stored at week level.


      Parameter selection: 4 week

      This means that 15 weeks need to be divided into buckets of 4-4 weeks and values should be aggregated. And output will be bar graph with 4 buckets (3 with 4 weeks data and 1 with 3 weeks)


      Similarly need this for 6 week and 8 week values. I have attached a sample data set for reference.


      My current approach: Bins

      I am using a bin created out of a calculated field with definition DATEPART('week', [Date]). Using this I am able to bucket values but there is no fixed logic as this function returns week number. So the buckets are not getting created in a sequential manner.


      Any help is appreciated(tagging you guys specifically because both of you have written posts on this topic earlier): Shinichiro Murakami @MARK FRASER


      Thanks in advance!