12 Replies Latest reply on Sep 2, 2014 7:01 AM by Burcu Y

Analysis with an aggregated table

Burcu Y Sep 1, 2014 6:13 AM

Hi I recently started working on Tableau and have been struggling with the same problem with the data I have.

Suppose I have a huge data model with a similar structure to the below where the values are aggregated and the time dimension is as below. I could create calculated dimensions or parameters to point to a specific column, but then I can't display all years. But what would be the best way to create a year dimension and to be able to get the result for that year.

Or is it a must to convert this to a structure where there is one column for year?

Country	2000	2011	2012	2013	2014
USA	3.5	3.8	4.1	4.4	4.7
UK	2.5	2.6	2.7	2.8	2.9
Germany	3.1	2.4	1.7	1	0.3
France	2.2	1.9	1.6	1.3	1
Italy	4.2	3.5	2.8	2.1	1.4
Greece	2.2	3.1	4	4.9	5.8

1. Re: Analysis with an aggregated table

Shawn Wallwork Sep 1, 2014 2:15 PM (in response to Burcu Y)

It will most certainly make your life easier is you reshape your data, producing a single column for year. What's your data source, not Excel I assume?

--Shawn
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Analysis with an aggregated table

Tableau kumar Sep 1, 2014 10:10 PM (in response to Shawn Wallwork)

Hi Burcu,

As Swan suggested, You need to rebuild the data.
Alternative is We need to Union all for the field "Year".

Thank you
Laxman Kumar
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: Analysis with an aggregated table

sree rao Sep 1, 2014 11:05 PM (in response to Burcu Y)

HI ,
Better reshape the database ..else create a combined field for all the years..

Regards
Sreenivas
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Analysis with an aggregated table

Burcu Y Sep 2, 2014 1:25 AM (in response to Burcu Y)

Hi
Thank you for the answers Shawn, Sreenivas and Laxman Kumar. Unfortunately its an excel sheet with aggregated data and I can't get the source data. And it has about 50 repeated columns, 500 hundred rows. I want to do the alteration as much on the tableau as possible as I will be receiving same sort of report.

I tried to workaround with combined field, but I am stuck. I attached the file with 2 sheets, one as is, one with my additional messed up combined field:) Could you please help me on that?

https://dl.dropboxusercontent.com/u/28569533/Book2.twb

Also as for using UNION, would it be possible to keep the year values as dimensions (2010, 2011 etc) after joining the columns? I would appreciate if you could give an example.

Thanks,
Burcu
Like Show 0 Likes(0)

Actions
5. Re: Analysis with an aggregated table

Shawn Wallwork Sep 2, 2014 4:47 AM (in response to Burcu Y)

That link doesn't work. You can use the 'Use advanced editor' in the upper right corner of a reply to get to the 'Attach' file link in the bottom right corner of a new dialog box to attach a file. Make sure it is a packaged workbook (twbx) not a twb.

--Shawn
Like Show 0 Likes(0)

Actions
6. Re: Analysis with an aggregated table

sree rao Sep 2, 2014 4:56 AM (in response to Burcu Y)

Hey ,
Unable to open the linl..

Regards
Sreenivas
Like Show 0 Likes(0)

Actions
7. Re: Analysis with an aggregated table

Burcu Y Sep 2, 2014 5:03 AM (in response to Burcu Y)
Sorry I managed to attach now, thanks Shawn!

Book2.twbx 25.5 KB
Like Show 0 Likes(0)

Actions
8. Re: Analysis with an aggregated table

Shawn Wallwork Sep 2, 2014 5:29 AM (in response to Burcu Y)
So the best way to deal with this is still to reshape your data. Fortunately for you there is an Excel Reshaper plugin you can use to do this in one click:

The New Reshaper...

In the attached I reshaped your data and easily created the table you seem to be wanting.

--Shawn

country.xlsx 10.8 KB

Book2 - SW.twbx 41.0 KB
Like Show 0 Likes(0)

Actions
9. Re: Analysis with an aggregated table

sree rao Sep 2, 2014 5:50 AM (in response to Burcu Y)
HI,
I have done aggregation at country level and attaching twbx file have a look and let me know is this helpful or not else tell me what your excepting so that i will work around..

Regards
Sreenivasa Rao.Aerasangi..

Aggrigate_result.twbx 35.4 KB
Like Show 0 Likes(0)

Actions
10. Re: Analysis with an aggregated table

Shawn Wallwork Sep 2, 2014 6:21 AM (in response to sree rao)

I'm just not sure what you're trying to accomplish. What does the viz look like? I'm totally lost.

--Shawn
Like Show 0 Likes(0)

Actions
11. Re: Analysis with an aggregated table

Burcu Y Sep 2, 2014 6:44 AM (in response to Shawn Wallwork)

Thank you Shawn this is an amazing tool!

The thing is, I have an excel sheet, I wasn't even sure how to load it. That's why I tried to figure out with a simplified table. Now I will apply the same to my excel sheet.

At least now I know there is no obvious way to map the objects in a way that it makes sense.

Thanks a lot!

Burcu
Like Show 0 Likes(0)

Actions
12. Re: Analysis with an aggregated table

Burcu Y Sep 2, 2014 7:01 AM (in response to sree rao)
Sreenivasa

I might have expressed in a wrong way, please excuse my vocabulary, as I am rather new to this. The table I have is "somehow aggregated" in the sense that the data is in a matrix rather than a table. Suppose what I wanted was to be able to show the same sort of TABLE as a visualization in Tableau, and continue from there.

If you have any recommendation on how I can achieve to go from the initial data I put, to the one attached without using Shawn's tool, that would be appreciated.

Thanks,
Burcu

Book3.twbx 14.4 KB
Like Show 0 Likes(0)

Actions