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. 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 Class L3 to L2 L3 to L1 L3 to L4 2018 43 8 16 2019 39 11 13 2020 20 8 44
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?
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.
Sure! Let me know If you've any query.
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!