3 Replies Latest reply on Jun 28, 2018 1:19 AM by Simon Runc

# Color treemap using table calculation| 2018.1

I'm currently trying to make a treemap that sizes based on the net assets of a product and colors based on the percent return within a user given time frame. I only have the price data for each product for every day, and have used that to create a formula to calculate the percent return in a time frame on a table (using the quick table calculation for percent difference), but when trying to bring this calculation to a treemap, nothing happens. The treemap only colors correctly when I drag in dates into the marks card under details, but this separates the boxes on the treemap and makes them ridiculously small. Is there any way to get the proper coloring I want while having only one treemap box per product?

(I'm unable to share the workbook as it has a lot of sensitive information on it)

• ###### 1. Re: Color treemap using table calculation| 2018.1

hi Alex,

So if the Table Calculation on Price, requires the Date in (in order to compute some difference between dates, say) then you will need to have Date in the level of detail for it to calculate how you want...and in that case as you are increasing the level of detail, Tableau will draw a square per date too.

What is you calculation trying to do? as their might be a way to achieve the same result without needing date in the level of detail

• ###### 2. Re: Color treemap using table calculation| 2018.1

Right now the calculation is looking at the price from the most recent date, finding the price from the earliest selected date by the user, and calculating the percent difference between the two (for all of the different products being looked at).

• ###### 3. Re: Color treemap using table calculation| 2018.1

hi Alex,

So what you can do is recreate this calculation as an LoD (I'll used FIXED LoDs as they are easier to understand....but for large data sizes it might be better to use INCLUDE/EXCLUDE)

Without seeing the data I'm guessing a bit...but I'll assume we have some products and prices on different days

[Start Date - Product]

{FIXED [Product]: MIN([DateField])}

[End Date - Product]

{FIXED [Product]: MAX([DateField])}

So we can the just get the prices on these 2 days, and compare them

[Start Price]

IIF([DateField]=[Start Date - Product],[Price],NULL)

[End Price]

IIF([DateField]=[End Date - Product],[Price],NULL)

and the final comparison

SUM([End Price])

/

SUM([Start Price])

-1

Or whatever comparison you want.

One last thing to note. As we are using FIXED LoDs they are computed before any regular filters are applied, so if you change the date range (by filtering on [DateField]) you'll need to make that filter a context filter (this bumps the filter up the calculation pipeline, and so is applied before the LoDs)

Hope that helps

1 of 1 people found this helpful