8 Replies Latest reply on Feb 6, 2017 9:36 AM by Jim Dehner

    Calculated Field

    Susan Kurian

      Hello,

       

      I have attached a spread sheet which contains test data. What I would like to do is create a filter with "School1" and "School 2" as a filter. If School 1 is selected then I need the Individual rows displayed for "Ed Cur" field, "Ed Ped" field and "Ed scd" with sum of values.

       

      And if School 2 is selected, I need individual rows displayed for  "Iaps Gen", "Iaps MHC" and "Iaps Phd" with the sum of values

       

      How would I accomplish that?

       

      Please Advise.

       

      Thanks,

      Sue

        • 1. Re: Calculated Field
          Jim Dehner

          Susan

           

          Could not open your excel file - says it is corrupt could you resend

          Jim

          • 2. Re: Calculated Field
            Susan Kurian

            Hi Jim,

             

            I have attached the file again.

             

            Thanks,

            Sue

            • 3. Re: Calculated Field
              Khang Pho

              Hi Susan,

               

              One method would be to pivot the columns so you can have the values in the rows.  From there you can create a calculated field like

              "

              IF [School Desc] = 'School 1' AND 
              ([Pivot Field Names] = 'Ed Cur' or [Pivot Field Names] = 'Ed Ped' or [Pivot Field Names] = 'Ed Scd')

              or

              [School Desc] = 'School 2' AND
              ([Pivot Field Names] = 'Iaps Gen' or [Pivot Field Names] = 'Iaps Mhc' or [Pivot Field Names] = 'Iaps Phd')

              THEN

              [Pivot Field Values]

              END

              "

              If you then filter out the nulls I think it should give you the desired effect.

              • 4. Re: Calculated Field
                Jim Dehner

                Hi Susan

                 

                Thanks for re-sending the data - I agree with with Kang that pivoting the data is the way to go

                I did it at the data source level leaving a set of values and field names

                Then I did set up a filter Show Data >> If Value >0 then 1 End

                Drag the filter to the filter shelf and set the value to 1

                drag fields to the columns and schools to the row

                Drag a schools tot the  filter shelf and show the filter - when you select schools the null field disappear

                 

                Here is a T10.1 copy

                 

                it should look like this

                 

                 

                 

                Let me know if that helps

                Thanks

                Jim

                1 of 1 people found this helpful
                • 5. Re: Calculated Field
                  Susan Kurian

                  Hi Jim,

                  Would you please show me, how did you pivot the data? Your Screenshot; looks exactly how I wanted to display it.

                  Thanks,

                  Sue

                  • 6. Re: Calculated Field
                    Jim Dehner

                    Good morning

                     

                    see the link below to the on-line hel

                     

                    Pivot Data from Columns to Rows

                     

                    I will walk you through it here

                     

                    Go to the data source tab

                     

                    Identify the columns that need to be pivoted (it you data)

                    Click on the header for the first column (Total Number)

                    Shift click the header for the column on the right most of your data (Ed scd)

                    While all the selected columns are highlighted Right click the last column and click on Pivot Data on the box that opens

                    the several columns that were highlighted will now become 2 columns that you can rename - I just chose Fields and Values

                     

                    That's all there is to it

                     

                    In your viz - Fields becomes a dimension that you add to viz and the Values are the Measures

                     

                    Let me know how it goes

                     

                    Jim

                    • 7. Re: Calculated Field
                      Susan Kurian

                      Hello Jim,

                       

                      It works like a charm.. Thank You so much.

                       

                      Sue

                      • 8. Re: Calculated Field
                        Jim Dehner

                        Glad to help you out

                         

                        If you need anything else let me know

                        Thanks for the badge  - always appreciated

                         

                        Jim