I have attached an example .twbx file as well as the excel data and a censored final product of what the report will look like.
Summary of what I want: 1) To have tableau calculate a rolling season to date sales figure by style # and by week #
2) For each week and style, take the rolling sales figure, add it to Store inventory and Warehouse inventory to get a total investment figure.
3) Find the maximum total investment figure so I can use my sell thru formula to return "% sold" (Current season to date sales / Total investment)
The closest I have been able to get to this in tableau is to use table calculations...which unfortunately isn't possible since aspects of the table calculation cant be brought into the view (like week # since the report is an aggregate season to date view.)
I want to do the above math in order to get as close as I can to the total amount of inventory that was available throughout the time period of my data set. This breaks out to: Every unit we have sold + all the units in stores + all the units in the warehouse. I cannot simply use current season to date sales and current inventory, since we have had a few poor selling styles transferred out of the system that will not show up in the current weeks information. I essentially want to find the high water mark for each style number throughout my data, and use that figure when computing my Sell thru Percentage.
For now, my (time consuming and very manual) solution has been:
1) Update my data set with the previous weeks sales data
2) Export sales for each style # by week into excel and create a rolling sales column
3) Update tableau with this rolling sales information
4) In the tableau sheet from step 2, bring in the newly created rolling sales, store inventory and warehouse inventory
5) Export the above into excel
6) Sum up rolling sales, store inventory, and warehouse inventory
7) Use the Maxif formula with the criteria being style # and the max range being the sum calculated above. This column is named "TTL Invest"
8) update tableau with the new TTL Invest figure
Obviously this is incredibly time consuming and usually takes up most of my morning since each tableau update takes about 15 minutes. My Monday mornings are getting filled up and this report is REALLY dragging me down so I would appreciate any insight on how to get this as automated as possible.
A couple of things to note... The week # is misleading because it actually spans two seasons. Weeks 44-53 were actually from last season and 1-23 are the current season. For the purpose of this report I am using the whole range, so starting at week 44 and ending at week 23. I made the calculated field "multi season week" to change 44 into -9 and 53 into 0 so I could arrange in chronological order.
Thank you for reading this far, I'm sure I could explain things better, so please let me know if any clarification is needed.