2 Replies Latest reply on Jun 1, 2012 12:34 AM by Laurent Moffroid

# What would the 2009 entity sales be if we apply its 2000 share of the total to the 2009 total?

Hi,

I am stuck with the following issues:

a) Say I have 10 entities over 10 years, from 2000 to 2009incl

Say years are in columns and entities are in lines and I look at their sales

I have created a calculated field to give the % of each entity sales in the total for each year, so bottom of the column is 100%

I want to freeze the first year proportion of the total and apply that to each following year total in order to get for each entity for each year a "simulated sales" number that is 'what would your sales be had you stick to your year 2000 % of the total?'

Then of course I would like to see the difference between the actual sales and the simulated sales

b) Coming back to these 10 years, let us say i have many entities and they can have from 1 to 10 datasets

I would like to create some bins like

1 dataset

2 datasets etc

and then return the number of entities that have 1 dataset, 2 datasets etc.

Thank you very much for your help!

• ###### 1. Re: What would the 2009 entity sales be if we apply its 2000 share of the total to the 2009 total?

Hello Laurent,

First of all, I think your data should be in a different format. The crosstab format, where you have a column for each year, is not an ideal foundation for a Tableau analysis. It should basically look like this:

Entity; Year; Amount

Entity A; 2001; 15,000

Entity A; 2002; 20,000

.

.

Entity B; 2001; 25,000

You can easily bring the dataset in that shape by using the data reshaping add-in. It can be found along with further explanations on this website: http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis

Once this is done, you can easily build another crosstab in Tableau that allows you to analyze the data further, and to apply the formulas you have mentioned. I think you can even get to the desired results by using some quick table calculations that are readily available, such as 'percent of total' or 'running total'.

Hope this is helpful to make a start,

Dennis

1 of 1 people found this helpful
• ###### 2. Re: What would the 2009 entity sales be if we apply its 2000 share of the total to the 2009 total?

Hi Dennis,

Thank you so much for your help, and most sorry for this late answer

Actually, I am working on sql and the db I am working with is structured the way you suggest in the first place, so I guess all I have to do is organise the data on my tableau sheet as you invite me to

Again, many thanks

Laurent