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

# Count when a dimension changes based on an ID

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!

• ###### 1. Re: Count when a dimension changes based on an ID

Hi Bryan,

One way to do this would be to convert the levels to an integer value:

RIGHT([Student level],1) > right click calc field > change data type > number (whole)

Then drag year to columns, SSID to rows and your new calculated field to text.

Right click your new calculated field > quick table calculation > difference.

This will show increase and decrease in levels between the years for each student.

Thanks,

Charlie

• ###### 2. Re: Count when a dimension changes based on an ID

Thanks, Charlie. This helps but it's not exactly what I'm looking for. This solution takes each SSID and displays a number like -1, 3, -2 and so forth depending on how much the individual SSID went up or down in that new calculated field trimming the field down to an integer rather than "L2".

What I'm really looking to do is display something like "43 students went from Level 3 to Level 2" and I'd planning on creating a calculated field for each possibility. So one would be "L3 to L2" another "L3 to L1" and so forth. I'd also considered defining this as parameters so the User could change the first parameter to "Level 1" and the second parameter to "Level 3" which would then show them the students who went from Level 1 to Level 3. This could spin out a list of SSIDs but ideally, the analysis is more "top level" so it would simply aggregate based on their graduating class which I've defined as sets and use elsewhere.

This table is super sloppy but it should illustrate the objective:

Graduating ClassL3 to L2 L3 to L1L3 to L4
201843816
2019391113
202020844

And again, I could just throw the SSID in there so you'd see the individual students in each class but really just a summary is all I'm looking for at this point.

Will your solution let me do this with some tweaking?

• ###### 3. Re: Count when a dimension changes based on an ID

Try this

Let me know If this help

Mahfooj

2 of 2 people found this helpful
• ###### 4. Re: Count when a dimension changes based on an ID

Oooh, this looks like it might do the trick. I'm away from my machine but I will test this out when I get back though reading through the logic it seems sound. Thanks, Mahfooj.

• ###### 5. Re: Count when a dimension changes based on an ID

Sure! Let me know If you've any query.

• ###### 6. Re: Count when a dimension changes based on an ID

Worked like a charm. I should be able to work with this elsewhere in my vizzes to display that information and perform other calculations on it. Thanks, again!