3 Replies Latest reply on Oct 9, 2012 1:23 PM by Mark Holtz

    Dividing String Fields

    Michael Lance

      Hello.

       

      I want to make a calculation that does the following with the "State Reading" (dimension) field:


      Divide all "State Reading" = "1" or "0" by "State Reading" = "1"

       

      I want to do this to add a percentage column to the right of the following:

       

      Column 1: "State Reading" = "1"  | Column 2: "State Reading" = "1" or "0"

       

      To make column 3 be the percent that column 1 is of column 2.

       

      Thanks,

      Michael

        • 1. Re: Dividing String Fields
          Mark Holtz

          Hi Michael,

           

          I'm a little confused.

          Do you have 2 measures both named "State Reading"?

           

          I'll refer to them as [0 or 1] and [Always 1]

           

          I believe you can create a calculated field as:

          sum([0 or 1])  / sum([Always 1])

           

          You can add all 3 columns to your view and format the calculation as a %.

          Does that help?

          • 2. Re: Dividing String Fields
            Michael Lance

            Hi.

             

            I've sanitized the data and deleted some of it to make it
            workable for this question (you'll notice the totals are different).

             

            Each row, as you'll see, has a student ID. I tried doing
            window_sum but that did not do what I wanted to do; I just want the grand
            totals row to be changed to fit the format of the rows above it. The bottom row
            should be like this:

             

            Column 1: number of records, which is the number of
            students proficient - with a "1" for State Reading (Fc/Sc Rc Mrdg)
            Column 2: Total, which is the number of students tested (Should be 199) Column 3: Percent of column 1 divided by column 2 (should
            not be 100% for the bottom row)

             

            Note: I will eventually try to do the same thing for
            several other fields, such as State Math (Fc/Sc Rc Mmth). I'm assuming I'll
            need to dashboard them all together when I finish. Is there a more efficient
            way to do this?

             

            Thanks!

            • 3. Re: Dividing String Fields
              Mark Holtz

              If I may attempt to restate what you are asking:

               

              1) Create a measure of "Tested" students as all records where the [State Reading (Fc/Sc Rc Mrdg)] field = 0 or 1

                 IF [State Reading (Fc/Sc Rc Mrdg)] = '0' OR [State Reading (Fc/Sc Rc Mrdg)] = '1'

                 THEN 1

                 ELSE 0

                 END

               

              2) Create a measure of "Proficient" students as only those records where [State Reading (Fc/Sc Rc Mrdg)] field = 1

                 IF [State Reading (Fc/Sc Rc Mrdg)] = '1'

                 THEN 1

                 ELSE 0

                 END

               

              3) Assess  Proficient / Tested

                 sum([Reading Proficient]) / sum([Reading Tested])

               

              You can do this for each dimension in addition to reading and display whichever fields you want in your table. You would not need to use any table calculations.

               

              Let me know if that's not what you're after.

              Cheers!

              1 of 1 people found this helpful