8 Replies Latest reply on Oct 31, 2012 9:04 AM by scott.ruffell

    Show all data points per page

    scott.ruffell

      Hey guys, so I'm putting together a report about revenue across multiple years and would love to be able to run a presentation that has all the data points for each client by year on a page.  As I have it now, it will only show one client's data set per "frame".  How would I change it to display all and go from page to page as I click next or click whatever is the page mover.  Thanks guys! KCCO

        • 1. Re: Show all data points per page
          Jonathan Drummey

          Hi Scott,

           

          Can you post a packaged workbook (.twbx) with some sample data, or at least a screenshot of the layout of view (including the Pages Shelf, Marks Card, and Columns and Rows Shelves), with what you'd like to see? I'm not clear on what the gap is between what you want and how the view is currently set up.

          • 2. Re: Show all data points per page
            scott.ruffell

            Hi, Jonathan.  I have inserted 2 images of what I currently have.  In one of them you can see I took our client names, we all know confidentiality agreements exist.  What I would like to do..mainly for the geographic map image is instead of having 7 different, separate spreadsheets for each year's revenue I would like to have it all on one spreadsheet and I can easily filter which year I want displayed.  I tired learning and messing around with the Pages but it simply showed me each zip code by itself, I need them all shown at once for a given year.

             

            And on the second picture, the bubble chart.  Basically the same thing, if I can show one year at a time that would be best.  Again, I have tried to use the filters but I cant seem to figure it out.

             

            In my excel workbook, I have 8 different spreadsheets, one for each year and one spreadsheet that has all years and info on it.  I have the dates as the top row and filled down those columns are revenue numbers. It is rowed by individual clients...so set up looks like Client - State - Zip Code - Revenue '06 - Rev '07 - Rev '08 and so on.  I hope this helps, I love this program, I'm just hoping I can figure it out for a good presentation to my boss.

             

            Map Example.pngBubble Example.png

            • 3. Re: Show all data points per page
              Jonathan Drummey

              There are a few options I can think of. The best solution for creating views in Tableau would be to have the year as a dimension instead of a different column. This is known as making your data "tall" instead of "wide". Tableau has a plug-in for Excel to do this, check out:

               

              http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

              http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

              http://www.youtube.com/watch?v=3iVy4QWw850 - first of a series of 3 videos on reshaping data for Tableau

               

              You can also reshape your data using Custom SQL and a series of UNION queries. Assuming you've done one of these two options, then the Year will be a dimension that you can do a quick filter on, or (as in the map) put on the Pages Shelf.

               

              Alternatively, using the spreadsheet that has all your data, you could create a parameter for the year, and then a calculated measure with a CASE statement that looks something like this:

               

              CASE [year parameter]

                   WHEN 2012 THEN SUM([10/15/2012])

                   WHEN 2011 THEN SUM([12/31/2011])

                   .... (other WHEN statements)

              END

               

              Then you would use that measure on the Columns shelf in the Shape chart, and show the parameter control.

               

              Jonathan

              • 4. Re: Show all data points per page
                scott.ruffell

                Hey Jonathan,

                 

                When trying to do the CASE [year] WHEN statement, I get an error telling me "Cannot use 'NULL' type in 'CASE' expression."

                 

                My CASE Statement looks like:

                CASE [Year]

                    WHEN [12/31/2006] THEN SUM([12/31/2006])

                    WHEN [12/31/2007] THEN SUM([12/31/2007])

                    WHEN [12/31/2008] THEN SUM([12/31/2008])

                    WHEN [12/31/2009] THEN SUM([12/31/2009])

                    WHEN [12/31/2010] THEN SUM([12/31/2010])

                    WHEN [12/31/2011] THEN SUM([12/31/2011])

                    WHEN [10/15/2012] THEN SUM([10/15/2012])

                End

                 

                I'm assuming it has a problem with trying to sum all the values in the date column.  The column header is the date of the year and below it are yearly revenues for each client; however, some clients do not have revenue for all years, would that be the problem?

                 

                Thanks again for your help! KCCO

                • 5. Re: Show all data points per page
                  Jonathan Drummey

                  If you use ZN(SUM([column])), I think that'll work. ZN zeroe's out the Nulls, so the CASE statemetn wouldn't be receiving any Nulls.

                  • 6. Re: Show all data points per page
                    scott.ruffell

                    Jonathan,

                     

                    Well, that helped but a new error popped up..of course.  However, the errors are getting a lot more logical.

                     

                    So, when I convert my [dates] to the Date type, it says "Sum is being called with (Date), did you mean (float)?"

                     

                    But, when I change the [dates] type to Numbers, it says "Expected type Date, found float. Comparison is CASE expression must be date type."

                     

                    I mean..I think I get what its saying, it needs to compare the date = date but I also can't sum a date.  But I'm trying to compare the column headers and sum the column data...

                    • 7. Re: Show all data points per page
                      Jonathan Drummey

                      Sorry, I should have caught that the first time around. The WHEN clause of your CASE statement needs to include the values of the Year parameter, not refer to measures (columns), and then based on those values it will return the appropriate measure. For example:

                       

                      CASE [Year]

                          WHEN "12/31/2006" THEN SUM([12/31/2006])

                          WHEN "12/31/2007" THEN SUM([12/31/2007])

                      ...

                      END

                      • 8. Re: Show all data points per page
                        scott.ruffell

                        That did the trick! Thank you for all your help Jonathan, really.  It made my experience with Tableau all that much better and the presentation to my manager will go even better!  Thanks again.