Do you have a workbook to show an example of what you are doing?
As I understand it, you want to compare any specific week across the five years of data to the first week of data without using a Table Calc? That sort of isolation of the data from the first week of data is probably easiest to do if the first week of data can be made its own data source for comparison purposes but seeing a bit more information about the data would be needed.
Hi Patrick sorry for the confusion as I am be confusing when explaining things.
So I want to look at the difference by percent change from week 2 compared to week 1 in ending inventory.
(ZN(SUM([# of apples])) - LOOKUP(ZN(SUM([# of apples])), -1)) / ABS(LOOKUP(ZN(SUM([# of apples])), -1))
I used that to calculate the percent difference from the previous week,
I then went to do a calculation that would look calculate the average percent change in inventory from example :2012,2013, and 2014 for week 2.
Example 2012 week 2: 3% change from week 1
2013 week 2: 5%
2014 week 2: 4%
average from 2012-2014 for week 2 would be 4%.
How would I have do that in a calculated field so I can apply it to a forecast in tableau? I am inserting all of this into a line graph just for context of what I am doing.
(ZN(SUM([Ending Unprocessed Receipts])) - LOOKUP(ZN(SUM([Ending Unprocessed Receipts])), -1)) / ABS(LOOKUP(ZN(SUM([Ending Unprocessed Receipts])), -1))
example.xlsx 27.8 KB
I'm not sure I understand your request correctly because the number is
quite different from your example in the post.
// Same as your calc
[Delta % from last year]
(ZN(SUM([# of Apples])) - LOOKUP(ZN(SUM([# of Apples])), -1)) / ABS(LOOKUP(ZN(SUM([# of Apples])), -1))
[Avg Delta from last week 5 years]
window_avg([Delta % from last year])
Talking about forecast,
I don't think you can use these calculated numbers as Tableau's "Forecast" functions dynamically.
But you can create another worksheet with using these numbers as independent Viz.
week_o_week_o_year_sm_9.3.twbx 34.4 KB