3 Replies Latest reply on Oct 3, 2016 12:23 PM by Yuriy Fal

# Complex table calculation

Hi, I need help with a complex table calculation. I have a table like this:

 Id day value 1 0 0.00% 2 -2 0.00% 3 4 0.00% 4 -3 75.00% 4 5 0.00% 5 1 33.33% 5 5 0.00% 6 0 100.00% 7 9 50.00%

For each id, I have a record which tells when (what day) the value changes. 0 is reference day, -1 is the day before, 1 is the day after. The values are between 100%-0% and the values can only to decrease. For example, for id 1 there is only 1 record, in the day 0 the value is 0.00%, meaning that on the days before (-1, -2, - 3 etc) the value was 100%, and on the days after (1,2,3 etc) it will be 0.00%. For id 4, there are two records, day -3 value is 75.00% and day 5 value is 0.00% (meaning that  on days<-3 value is 100%, on days -3 to 4, value is 75.00% and on days>=5 value is 0.00%.

I need to calculate in Tableau the Average value for all id, so I need the values for each id in all days, the result should look like this:

 day average value -3 96.4% -2 82.1% 0 67.9% 1 58.3% 4 44.0% 5 28.6% 9 21.4%

• ###### 1. Re: Complex table calculation

Hi florin,

I've binned [Day] field and set "Show Missing Values" to ON

to trigger a domain padding (to get all days from -3 to 9).

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Complex table calculation

Thanks Yuri!

• ###### 3. Re: Complex table calculation

Florin, you're welcome.