CODEWITHSUNDEEP
tableau-apicalculated-columns
May
May

Reputation: 29

Tableau compute the diff between first and last column of the current table view

I need to do a task similar to the following scenario. Let's say I have a file of 10 products' sales from 2000 to 2014, and our internal product rank of importance (from 1-10). So the data file has 4 variables, product name, internal rank, sales, year, e.g., for product1:

  1. product1, 5, 1000, 2000
  2. product1, 5, 2000, 2001
  3. product1, 5, 3000, 2002
  4. ...

Some products have sales data missing for the first few years.

The table I have now on Tableau worksheet displays each product in a row, followed by the internal rank, each year's sales from 2000 to 2014, and the 15 year average per row/product. e.g., for product1:

  1. product1, 5, 1000, 2000, 3000, (2003sales...2014sales), average sale 2000-2014

I want to add another column that shows the difference between the first available sales year and 2014, e.g., product1 has complete data, so this column would be sales difference between 2014 and 2000. product2 sales started in 2002, so this column would be difference between 2014 and 2002.

I guess some LOD codes may do the trick? Could anyone please help? The standard table calculation isn't helpful, or maybe I haven't figured it out. Thanks so much!

Upvotes: 1

Views: 5809

Answers (1)

Alexander
Alexander

Reputation: 1967

You could do the following:

  1. create 4 calculated fields [2000], [2001], [2013] and [2014] with the formula:

    IF [year] = 2000 THEN 
[sales] 
END

    for each field you replace the year with 2013 or 2014

  2. create another 4 calculated fields [Sales2000], [Sales2001], [Sales2013] and [Sales2014]:

    {fixed ProductName: sum([Sales2000])}

    again change the year accordingly

  3. create the calculated fields for the difference [diff2000-2014] and [diff2013-2014]:

    IF Isnull([Sales2000]) THEN 
[Sales2014] - [Sales2001] 
ELSE 
[Sales2014] - [Sales2000] 
END

    and

    [Sales2014] - [Sales2013]

So what you did was

  1. You extract the values for the years you need

  2. You assign them to all rows with the product

  3. you calculated the difference you wanted

  4. If you want it in the worksheet now, drag the [diff2013-2014] field inton the columsn shelf and select MIN(), MAX() or AVG() since you don't want to add up all the differences.

Upvotes: 1

Related Questions