6 Replies Latest reply on May 31, 2016 4:08 PM by Bryan Mills

    Count when a dimension changes based on an ID

    Bryan Mills

      I’m having a heck of a time with this.

       

      Students are identified by an ID # and are assigned a level (L1, L2, L3 or L4) based on how they score on a test each year (the score of the test is irrelevant for this question, just the [Student Level] they are assigned based on their score). There are two years of data, 2015 and 2016. So the data looks like this:

       

      SSIDYearStudent Level
      12342015L3
      56782015L4
      99992015L3
      12342016L2
      56782016L3
      99992016L4

       

      I’d like to know how many students went from one level to another from 2015 to 2016. So for example, of those who were assigned L3 in 2015, how many then scored L2 in 2016, counting at the student ID level [SSID]. I thought this was an LOD calculation, fixing it on the student ID, so I tried this out with no luck:

       

      {fixed [SSID]: SUM(IF [Year] = 2015

      AND [Student Level] = “L3”

      AND [Year] = 2016

      AND [Student Level] = “L2”

      THEN 1 END)}

       

      I realize the issue is that I'm asking the calculation to find an entry with 2 years and scores and since the data are one row per student per test per year, this won't work. I thought by fixing it to the SSID this might do the trick but I've never used LOD expressions before.

       

      Any suggestions? Thanks!