4 Replies Latest reply on Feb 21, 2012 12:02 PM by Sean Trout

    Panel Charts - controlling the number of rows

    Sean Trout

      I've been working on a visualization based on the example previously published by Andy Cotgreave (http://www.thedatastudio.co.uk/blog/the-data-studio-blog/andy-cotgreave/panel-charts-in-tableau).  I am attempting to use the panel charts to develop a heat map that will rank customers based on total sales for a given quarter, then have the color scheme based on the percentage change in sales from the previous quarter.  It would look very similar to this viz - http://www.stockmapper.com/NYXtrac.html.

       

      I'd like to give the user an option as to how many customers they'd like to see (25, 100, 225, 400, 625 or 900).  All of the options in the list can be broken down into a square.  The thing I'm struggling with is getting the count of customers to be limited to the count selected by the user.  I just haven't been able to find a filter that works.  Is there a way to control the count of customers via the Column & Row (or RowVal) calculations? 

       

      Due to confidentially, I am not able to post the data set that I am working with.  However, the example presented by Andy is essentially the same thing I am doing.  I'm just using different marks.

        • 1. Re: Panel Charts - controlling the number of rows
          Nathan Krisanski

          Hi Sean,

           

          You can create a Parameter for allowing the user to select the number of rows to show. This can be an open number field or a select list with pre-defined values (25,100, etc).

           

          Now comes the tricky part....

           

          I've had success with this before creating a Table Calculation - Running Total on a field that will only return 1 for each row. This could be a distinct count of customerid perhaps. Will be 1 for each row and then the running total will increment it by one. Make sure you tell it to compute using Table Down.

           

          If you also do a sort on customerId by salesVolume desc, you will get a numbered list next to your rows. This in itself can be handy for showing a ranking report that can be dynamically generated in Tableau.

           

          Now if you go to Edit Table Calculation and Customize, you get the option to create a Calculated Field using this rank number. It may start with something like: RUNNING_SUM(Countd([CustomerId]))

           

          Update the Formula to be: If RUNNING_SUM(Countd([CustomerId])) <= [UserDisplayRows] then 'Show' else 'Hide' end. Then Name this field RowFilter and OK.

           

          Now drag your RowFilter calculated field to the filters shelf and filter to 'Show'. --Again make sure the filter is set to compute using -> Table Down.

           

          Thats IT! now you can update your parameter value and the number of rows showing will update automatically.

           

          SIDE NOTE:

          If you have a total on your table, this will be affected by your filter as it will only show total of rows being shown. To get around this, don't place the RowFilter on the filter shelf, but put it on your row/column shelf. You should see your top rows displaying 'Show' in that column and the rest showing 'Hide'. Right click on a Hide row and chose the Hide option. This will take away the rows above your selection, but leave the grand total in tact.

          • 2. Re: Panel Charts - controlling the number of rows
            Tracy Rodgers

            Hi Sean,

             

            Similar to Nathan's answer, instead of using running_sum(countd([fieldname])) the index function can be used as well. The index function returns the the current row in the partition. So using a similar calculation where this function is used with the parameter, and placing it on the filter shelf will return the same outcome. As Nathan stated, make sure that the calculation is computed using Table Down.

             

            If Index()<= [UserDisplayRows] then 'Show' else 'Hide' end

             

            -Tracy

            • 3. Re: Panel Charts - controlling the number of rows
              Sean Trout

              Thanks Nathan.  I will give this a try and let you know how it works out.

              • 4. Re: Panel Charts - controlling the number of rows
                Sean Trout

                Thanks Tracy.  I'll give it a try.