2 Replies Latest reply on Jan 7, 2013 3:22 PM by Alex Kerin

    Dynamic Grouping (to group number of days)

    Rina Bongsu-Petersen

      I'd like to learn how to group dynamically. My data file has the application dates (I work in higher education / admission). I have a calculated field that count the number of days the application has been received. I then manually created 4 groups, ie. 1-15 days, 26-30 days, 31-45 days and 45+ days. As we continue to receive more files, the number of days that weren't exist before, will need to be put in the relevant groups. For example, on Jan 1, in 1-15 group, 13 didn't exist. On Jan 8, 13 shows up. I would then put 13 in the 1-15 group manually. This becomes taxing when it comes to 45+ days, because it could be from 45 days to x days. 

      So instead of  manually grouping it, how can I group it dynamically?


      Any help would be greatly appreciated. thanks!



      Rina Petersen

        • 1. Re: Dynamic Grouping (to group number of days)
          Joshua Milligan



          I'm wondering if you could simply have a calculated field that would assign a group?  It would look something like:


          IF Days_Since_Received <= 15 THEN "1 - 15"

          ELSE IF Days_Since_Received <= 30 THEN "26 - 30"




          But I'm not sure exactly what you want to do with 45+ or if there are other considerations with your data or exactly what you are trying to achieve.


          Do you happen to have a packaged workbook you could post?

          • 2. Re: Dynamic Grouping (to group number of days)
            Alex Kerin

            If you have a limited number of buckets, Joshua has the ideal answer. If there are more, or you wanted to be more finite than 15 days, you could also dynamically create the groups using a calculation. See the attached - it uses a parameter as the base date and a calc to work out the number of 15 day blocks:


            str(int(datediff('day',[Base Date],([Order Date]))/15)*15)+" to "+str(int(datediff('day',[Base Date],([Order Date]))/15)*15+15)+" days"


            The essence is int(datediff('day',[Base Date],([Order Date]))/15)*15 which is give me the number of days between the date and the base date, divide it by 15 and take the integer value (i.e. give me the number of 15 day blocks), multiply this by 15 to get the numbers 0,15,30 etc.