3 Replies Latest reply on May 31, 2012 10:18 AM by Hari Venkat

    Hide a column without excluding it

    Hari Venkat

      I searched through the forums and found this couldn't be done (as of 2008), but I'm hoping the feature might be in now.

       

      I'm doing some calculations across survey data. It's like this:-

       

      Question | Response number | Count (Response)

       

      Q1           |         1                 | 10

                     |         2                 | 14

                     |         3                 | 11

       

      I calculated (Response number * Count (response)) / Total(Count(Response)), which is a weighted average (the weight being the response number dimension itself, a satisfaction rating). I wound up with something like this:-

       

      Question | Response number | calculation

       

      Q1           |         1                 | 2.028

                     |         2                 | 2.028

                     |         3                 | 2.028

       

      Similarly for questions 2, 3, 4, etc.

       

      Now I want to visualize this, but the response number dimension is no longer required; I just want to visualize the questions and the average response. Removing the Response number column quite naturally destroys the table calculation, since the weighted average depends on the response number.

       

      The closest I got was using a filter to display only the Max(response number) and so keep Question and average in one row (and so somewhat usable in other charts) but I'm hoping there's a better way to get what I need.

       

      What I'm looking for in essence is

       

      Question  | Calculation

       

      Q1           |   2.028

       

      Either by calculations / table formatting. Is this possible to achieve?

        • 1. Re: Hide a column without excluding it
          Beni Djohan

          Hi Venkat,

           

          It seems like the problem can be solved using calculated field, but you will be doing an aggregated calculation.

          say, you create a formula CalcX, with the formula SUM(Weight*ResponseCount)/Sum(ResponseCount).

          then the visualization is created by putting the Question on Row and CalcX on Marks/Label

           

          Hope i understand you correctly.

           

          Rgds

          • 2. Re: Hide a column without excluding it
            Tracy Rodgers

            Hi Hari,

             

            I hope I'm not pointing out something you already  know, but one thing that can be done is to highlight all the rows of the Response Numbers except for the top one, then right click and select 'Hide'. Then, right click on the Response Numbers pill and un-check 'Show Header.' Hope this helps!

             

            -Tracy

            1 of 1 people found this helpful
            • 3. Re: Hide a column without excluding it
              Hari Venkat

              @Beni

               

              The problem is that the weight is response dimension itself. Response * Count(Response) won't work, because of the "can't mix agg/non-agg calculations" error.

               

              @Tracy

               

              That's a neat trick, didn't know that, thanks! It's great for presentation, but would give problems when you have a hierarchy in place.

               

              I did figure out a way; I used a set that contained all values of response, constructed the table. The set, unlike the response dimension, could be hidden. For the agg/non agg mix calculation, I used Min(response) * count(response). Since it was broken down by response itself, min(response) would always be equal to the response value.

               

              To eliminate the duplicate average values, a simple calculated field that filtered out all but the max(response) within the pane. It still takes a little adjustments to properly visualize different charts on the fly (since response dimension can't be eliminated from the charting) but it's doable.

               

              Thanks to the both of you for chiming in!