I am trying to find a solution to slice a week window based on the current week number, and for the next 3 weeks, i.e, Week 9 uptil Week 12.
Let me explain things using the screenshot below.
1. Original Data
This is how the source data is. There is no Date column out of which I am extracting the Year and Week using Datepart function. The idea here is, if a cell in a row is blank, it needs to be filled up with preceding cell's information for that material.
2. Final Data
I am able to achieve the above result using the Previous_Value() function. As you can see for material 'MNO' all the columns that are null after Week 52 are filled with the Week 52's data. Same goes for material XYZ.
3. Final Window
Now, the 'Final Data' view yields successful results for me. The complications occur when I have to show a sliced window of just 4 weeks starting the current week onwards. In here, you can see that the results are matching up with how they were showing up in the 'Final Data' section. These results have been achieved using the INDEX() function of Tableau, where I using the following calculated field to get a static window - 'LAST() <= 6 AND LAST() > 2'. Now, there is no assurance that next week only Week 16 data will be populated into the sheet from the data source. You might see data uptil Week 25, and based on the use of the calculated field using LAST(), the window to display (4 weeks out from current week number) might change to something else.
1. I am looking for a solution to get a dynamic window based on current week number and 4 weeks out. I used the following formula to generate the results and get data for just the material 'ABC' as it is not pulling in any data from the previous week.
IF [Week] >= DATEPART('week',TODAY()) - 1 AND [Week] <= DATEPART('week',TODAY()) + 2 THEN 'Show'
2. Having a static filter using the INDEX() function eliminates the 'Material' filter I have on the dashboard. Check the attached workbook and filter just the single materials 'MNO' and 'XYZ'.
3. I am also looking to find another solution to exclude any material that has all positive values in the sliced window from showing up in the final window. Since, XYZ has all positive values of 2 for all 4 weeks, it should not show up.
4. I have to adhere to the ISO Week standard where the date '2/29/2016' falls in Week 9 instead of Week 10, and so on.
Sorry for having such a convoluted issue, but my project is stuck because of this minute thing. Thanks for looking into this anyways.
P.S.: I cannot upgrade to anything above 9.0, so please screenshot me your solutions.
Slice Week Window.twbx 27.9 KB