2 Replies Latest reply on Aug 9, 2016 9:52 AM by Bryan Mills

    Need help with a LOD expression pertaining to calculating growth

    Bryan Mills

      I am calculating a student's test score growth (identified by a dimension in the data called SSID) so I may evaluate a specific class (cohort) and how many students are reaching their growth targets each year. This is accomplished by evaluating a student's level in 2015 and then looking up a growth target (a lookup table that is joined in the datasource) and doing all the various calculations based off of that.

       

      I've been struggling with this for a while and while I have my growth calculations working as intended at the student level, I cannot get them to aggregate properly at the school level. I admit that the way I've done this is probably not the most efficient and rather messy...but at least it works...mostly It's a somewhat complicated process to calculate growth that is dependent on the Student Level variable and I made due but finding similar examples out there has been super tough and so this is the best I could come up with by myself.

       

      In the attached workbook, the Student List viz calculates as intended. The 0-line of the chart is in effect their growth target and how far to the left or right they fall indicates how far below or above their growth target a student was. Donald Duck Elementary for example has 96 rows (students), 11 of whom had a Growth Gap of < 0 (which indicates you did not hit your target) while 85 students had a Growth Gap of >= 0 so 88.5% of the students met their target. But, when you go to the School List viz it shows that there are 103 students at Donald Duck Elementary and just 57 had a Growth Gap of >=0 or 55% and both sheets are using the same set of filters. The measure I'm using is simply taking that Growth Gap calculation and fixing it at the SSID level which I assumed would aggregate the results by building but there's something I'm missing and I can't figure it out...

       

      Can someone tell me where I may be going wrong here? And if anyone has any thoughts on a better / more efficient way of calculating growth based on a different target per student based on their Student Level in 2015, I'm all ears!

       

      Thanks Tableau community!

        • 1. Re: Need help with a LOD expression pertaining to calculating growth
          Vasil Petkov

          Hi Bryan,

           

          You should include School Name in your calculations.

           

          Attempted Test:

          IF { FIXED [School Name],[Ssid],[Test],[Cohort]: [Growth Gap]} > -500

          THEN 1

          END

           

          Met Growth Target SCHOOL

          sum(IF { FIXED [School Name],[Ssid]: [Growth Gap]} >=0

          THEN 1

          END)

          /

          sum(IF { FIXED [School Name],[Ssid]: [Growth Gap]} >= -500

          THEN 1

          END)

           

          Best,

          -V

          1 of 1 people found this helpful
          • 2. Re: Need help with a LOD expression pertaining to calculating growth
            Bryan Mills

            Thanks, Vasil and apologies for the delayed reply. This helps and I didn't realize I needed to include School Name in the calculations. I am going to dig into it and see if this solves the problem!

             

            Question: will I need to do similar FIXED functions and fix on school name throughout the calculated fields? That [Growth Gap] has several calculated fields inside of it and I'm just curious if I need to have the same sort of layout for each of the calculations fixing on School Name or if I can just do it on the [Met Target School] calculated fields? The goal is that I want to do the various calculations on the SSID level but aggregate at the school level...which I'm pretty sure you know but I'm just wondering if I'll do this for each calculation or just the one that I'll be throwing into the viz to display.

             

            Thanks, again!