9 Replies Latest reply on Apr 27, 2018 1:44 PM by Jim Dehner

    Aggregate Calculated Field Question

    Bryan Egan

      Hi!

       

      I created a calculated field earlier today that is simply SUM([Successful Classes]) / COUNT([Enrolled Classes)] to find the overall success rate for students. I would like to though create a visual comparing development student success rates vs the entire student population....OR the student population sans development students.

       

      My goal was to create two metrics....

       

      Metric 1:

      If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

       

      Metric 2:

      If [Development Student] = 'No' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

       

      Unfortunately both are bringing up the same error; Syntax error (maybe you are missing an operator)

       

      Just wondering what part of code I am missing to make this work.

       

       

      Also, on a semi-related note; is it possible to create a visual where I could use the aggregated success rate but break it out by Dev students in a line graph including other metrics?

        • 1. Re: Aggregate Calculated Field Question
          Jim Dehner

          Hi

          the issue is a misplace )

           

           

          If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes)] end

           

          should be in each statement

           

          If [Development Student] = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes]) end

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Aggregate Calculated Field Question
            Bryan Egan

            It appears to have not. Now the error coming up is "cannot mix aggregate and non aggregate comparisons or results on "if statements"

             

            Any idea what I can do?

             

            Thanks,

             

            Bryan

            • 3. Re: Aggregate Calculated Field Question
              Jim Dehner

              sorry

              in addition to the first issue there was a second - [development student ] needs an aggregation - attr()

               

              If   attr (   [Development Student]   )  = 'Yes' then SUM([Successful Classes]) / Count([Enrolled Classes]) end

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Aggregate Calculated Field Question
                Bryan Egan

                Not your fault! So it appears to have worked.....BUT (there's always a 'but') none of the values are showing up! Here are two snapshots of the work. I am sorry for the hassle but thank you for the help!

                 

                 

                Bryan

                • 5. Re: Aggregate Calculated Field Question
                  Jim Dehner

                  I would actually like to see the data for [successful classes] and [enrolled classes]

                   

                  The typical cause of no values showing up is Null values in the data - to get around that you can try wrapping the dimensions in ZN()

                   

                  If   attr (   [Development Student]   )  = 'Yes' then SUM(  zn([Successful Classes])) / Count( zn([Enrolled Classes])) end 

                   

                  I have never used zn() with count so you may get an error - try with and without

                   

                  also in your formula are you are counting all the classes and not the distinct classes - that may be what you want to do but if not then use countd()

                   

                  Jim

                  • 6. Re: Aggregate Calculated Field Question
                    Bryan Egan

                    Here you go.

                    Feel free to give some constructive criticism on the dashboards and other visuals. And any ideas for what I should also try to show.

                     

                    I am relatively new to Tableau and this is my first big time project for work. I need to make the best impression imaginable.

                     

                    Thanks again!

                    • 7. Re: Aggregate Calculated Field Question
                      Jim Dehner

                      OK

                      see the attached

                       

                      there is good news and bad news here

                       

                      first the good news - is this what you wanted?

                       

                      first the change to the dev stud success rate

                       

                      then the more difficult part - you have an indicator in the data set - Development Student Population - that is a 0 or a 1

                      and you are using it as a way to split the data set into 2 parts -

                      as long as you only use a single part in the viz (eg 0 or 1)

                      you can do use a filter to sort out the other part and get the Development rate

                       

                      but if you include the 0 this is what you get

                       

                       

                      the usual way to do this is to duplicate the data set and use a Union between the 2 data sets - it creates 2 tables and you use one to do the "0" calculations and the other to do the "1" calculations

                      I'm hesitant to suggest that route unless needed because you have 3 data sources and a lot of sheets here and I don't know how much will be affected

                       

                      Jim

                      If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                      • 8. Re: Aggregate Calculated Field Question
                        Bryan Egan

                        The reason why I was trying to do it the other way was for ease of use. I would like to be able to have a graph where I can look at the success rate for dev students and the success rate for all other students and potentially een loop in the success rate for all students in comparison. Right now, like you alluded to, I can create a filter based on whether the student is a dev student or not. While that is all well and good for a number of things I will be showing, I would prefer to be able to show them both at the same time without worrying about filtering.

                        • 9. Re: Aggregate Calculated Field Question
                          Jim Dehner

                          see the attached

                           

                          I did an example - I don't know how this will play with the other sheets -

                           

                          just to show you where we are going here is the text table output

                          It filters you can select dev3 and dvev31 - i'll explain and yes you can change those

                           

                           

                          you start with creating a union of the daata with a  itself

                           

                          on the Data source tab just drag your sheet till you see the red bog drop to junio then drop

                           

                          the data in now copied into 2 table and there are 2 new columns - one identifies the source data sheet and the other uniquely identifies the tables and dev3 and dev3.1 in this case

                           

                           

                          Now when you go to a worksheet you have a dimension that you use to separate the same data for 2 different purposes

                           

                          in the example I'm not certain all the math and logic around your internal metrics makes sense - but it will serve as an example

                           

                          I used dev31 and the data set for Dev stud population =1 and dev3 and the set for dev stud pop =0

                           

                          First recalculate the successful class and the total call values as

                           

                           

                          then the success rate is a simple division

                           

                           

                          That is how you can do it - but it may have messed up other things - your workbook is very complex

                           

                          Jim

                           

                          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.