4 Replies Latest reply on Dec 13, 2017 9:24 AM by Kiran Timmasarthy

# Challenge: how can I create a 3 week trend in a table (YTD)?

I need to create a table that shows the positive or negative trend for every production line in a multiple factory database. I should do so by comparing the YTD productivity value for a certain week to the YTD productivity value of 3 weeks back.

My calculated fields are:

``Prod = [Prod Time]/[Week Time]  Prod YTD = RUNNING_SUM(sum([Prod Time]))/(RUNNING_SUM(sum([Week Time]))) 3 week trend = ZN(LOOKUP(RUNNING_SUM(sum([Prod Time]))/(RUNNING_SUM(sum([Week Time]))),[WeekSelect])) - ZN(LOOKUP(RUNNING_SUM(sum([Prod Time]))/(RUNNING_SUM(sum([Week Time]))),[WeekSelect]-3)) ``

I use a "WeekSelect" parameter that takes the values of the specific Week Number.

My calculations only show 0% on the table for now and I have tried many alternatives (also without the parameter, calculating by cell, week, etc) but haven't succeeded.

Please, see the attached sample file.

I am using Tableau Desktop 9.1

Any help will be greatly appreciated! Thanks!

• ###### 1. Re: Challenge: how can I create a 3 week trend in a table (YTD)?

Hi Arnau,

For table calculations to work as expected,

one should have the necessary Dimension fields

somewhere on a view (Rows/Columns/Details/Pages).

The [Week Number] is absent on your 3w view,

it is on a Filter shelf only, and this is not enough.

Moreover, if you want to show only one [Week Number] on a view,

and you're calculating LOOKUP() along this field,

you have to use Table Calc filter -- to hide Marks

(but not Filter them out from a view completely).

Please find the attached w/Sheet 7 as an example.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Challenge: how can I create a 3 week trend in a table (YTD)?

Thanks a million Yuri!!

Your answer is just what I needed, you can't imagine how thankful I am!!

Best wishes to you and hopefully this thread will help someone else in the future too.

Regards,

Arnau

• ###### 3. Re: Challenge: how can I create a 3 week trend in a table (YTD)?

Arnau, you're welcome.

• ###### 4. Re: Challenge: how can I create a 3 week trend in a table (YTD)?

Hi Guys

Could you help me similar problem, My client requirement show 4 weeks trend like excel, Please suggest me how produced in tableau and how it will calculate, Thanks.