7 Replies Latest reply on May 8, 2015 11:06 AM by Jared Raymond

    How to subset data into groups that change over time?

    Jared Raymond

      I'm working with school data, so I want to group elements of the data into subsets based on grade. (ie PK, K, G1-12) However, this analysis is over time, so therefore it'd change by year. So in other words, whomever is in grade 1 in 2015 will be in grade 2 in 2016 and grade 3 in 2017 etc. Do you have any advice in starting/accomplishing such a task?

        • 1. Re: How to subset data into groups that change over time?
          Alexander Mou

          Two approaches:

           

          1.Labeling the students dynamically by creating a calc field LABEL

          if age<6 then 'PK"

          elseif <7 then 'K'

          else...

          end

           

          2.Create set for each of the group.

           

          Both approaches are dynamic upon data.

           

          On Fri, Apr 24, 2015 at 3:08 PM, Jared Raymond <

          • 2. Re: How to subset data into groups that change over time?
            Jared Raymond

            Would this be dynamic for each year? For example, if age < 6 then PK, but what about the next year? Does that mean that a new group where age < 6 is PK, or that the group what was in PK moves to K? In other words, I don't think that the age perfectly defines the group (ie kids could be 6 and in K or 6 and in Grade 1, so is there a way to define a group and then have it move with time? Such as group PK = (definition) then in year Y+1 that group = K, y+2 = 1.

             

            There are so many pieces in this one I'm not sure what to try and solve first.

            • 3. Re: How to subset data into groups that change over time?
              Jared Raymond

              In other words, the groups have already been created by class. It's just that they don't move over time, or almost seem to move backwards. I'm looking at years over time (say class years in 2015, class years in 2020, class years in 2025) etc and it's not registering that those who are in this year in 2015 will be in that year in 2020. I have the grades sorted and defined, I just don't know how to get the grades to move with projections of time in the future.

              • 4. Re: How to subset data into groups that change over time?
                Alexander Mou

                Could you mock up an excel or twbx workbook ?

                 

                On Saturday, April 25, 2015, Jared Raymond <

                • 5. Re: How to subset data into groups that change over time?
                  Jared Raymond

                  What I tried to do was set up class labels by using a graduation year field and a current year type field.

                   

                  Something like:

                   

                  if graduation year = current year then senior. (It might have to be = 1 else 0 end)

                   

                  if graduation year - current year = 1 then junior (or =1 else 0 end)

                   

                  The problem is that I get "cannot mix aggregate and non aggregate arguments with this function"

                   

                  What I want to do makes sense in my head but I have no idea how to make the data, or Tableau match what's in my head.

                   

                  I'd send sample data if I could, both on the grounds of it being too advanced for me to mockup, and privacy concerns with sending the actual file.

                  • 6. Re: How to subset data into groups that change over time?
                    Jared Raymond

                    I also have a bunch of years as columns, and I wanted to have specific dates within years, ie Sept 1 - Aug 31 of those years. Is there a way that I can convert those years into that range?

                    • 7. Re: How to subset data into groups that change over time?
                      Jared Raymond

                      If anyone is interested, what worked for us was to do something like:

                       

                      IF (INT(Year([Variable Year Numeric]))) = (IF MONTH(NOW())>8 THEN YEAR(NOW()+1)ELSE YEAR(NOW()) END) THEN "Group 1" ELSEIF (INT(Year([Variable Year Numeric]))-1) = (IF MONTH(NOW())>8 THEN YEAR(NOW()+1) ELSE YEAR(Now()) END) THEN "Group 2" ELSEIF......etc etc

                       

                      Essentially we were using the year to determine groups, and the year is split so that certain months within a year go into different group buckets.

                       

                       

                      And this was set up by another variable that was:

                       

                      IF[Variable Date] > #9/1/2012# AND [Variable Date] < #8/31/2013 THEN "Group1" ELSEIF [Variable Date] > #9/1/2013# AND [Variable Date] < #8/31/2014 THEN "Group2" etc etc

                       

                      Just wanted to close the question and hopefully help someone down the line.