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.
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.
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://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)
Then you would use that measure on the Columns shelf in the Shape chart, and show the parameter control.
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:
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])
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
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.
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...
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:
WHEN "12/31/2006" THEN SUM([12/31/2006])
WHEN "12/31/2007" THEN SUM([12/31/2007])
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.