There are probably multiple ways to solve your problem, but I think the below--based on table calculations--should work well in different views.
1. Make sure the raw date [Date] "201101" is in the dimension category.
Generally, dates should be dimensions, but this is also necessary to specify [Date] as a partition for the WINDOW function below to find max week.
2. Create [Date Year] and [Date Week] fields and put in hierarchy.
I left them as strings and used Tableau's string functions RIGHT, LEFT, .. This step may not be strictly necessary, but I think it's useful and may simplify your view creation.
3. Create table calc for [Week Max].
[Week Max] = INT(RIGHT(STR(WINDOW_MAX(MAX([Date]))), 2))
This finds the maximum date and then extracts the week number. I convert this as an INT so that I can use it in the YTD field below.
The key to table calculations is the Compute Using option which specifies the partition that the expression is computed over. We want [Week Max] to be computed over all dates in the data. And if you add additional dimensions, such as Date Year or Date Week, you may need to revise the table calc > Compute using advanced > and add all of these dimensions to the right-hand side. This can be confusing, but the "Description" Tableau provides on the Edit Table Calc screen is helpful.
4. Create table calc for [YTD Sales]
IF FIRST() == 0 THEN
IF INT(RIGHT(STR(ATTR([Date])), 2)) <= [Week Max]
Starting inside, you can see the core SUMIF type function. The ATTR() function is an aggregate, but returns only a single value or * if there are multiple values. WINDOW_SUM sums these values, and the FIRST() == 0 means that the value is returned only for first row in the partition --- this prevents the overprinting you often see.
Again, the compute using is key. Here we want to compute using [Date Year] and [Date] (in that order), resetting every [Date Year].
5. Creating the view.
Add [Date Year] to the rows shelf. Add [Date] to level of detail. Add [YTD Sales] to the Text shelf. Since [YTD Sales] also includes [Week Max] this is called a "nested table calc." Note, however, you don't need to explicitly include [Max Week] in the view.
You'll probably see only one value in the table. This is due to the compute using not being set. Right-click on [YTD Sales] > Edit Table Calculation. In the "Calculated Field" Box, you'll see a pull-down menu with [YTD Sales] and [Week Max].
Select [Week Max] > Compute using advanced > and move all of the date fields to the right-hand Compute Using side.
Select [YTD Sales] > Compute using advanced > and verify [Date Year] and [Date] or on the right-hand side in that order. Click OK, then select "Restarting every" Date Year.
Now you should have:
Phew! Again, there may be other ways of doing this and if you have a more specific view in mind, feel free to post it and see if someone else comes up with a better solution. But table calculations are critical to using Tableau, and this is a nice example of the importance of the Compute using aspect of table calcs.
YTD.twbx.zip 23.6 KB
Jim - Thank you for your response and sorry I haven't got back with you quicker. I was out of the office yesterday so only able to try your solution today.
From looking at your workbook it seems to work exactly how I want it to work. However, for the life of me, I cannot recreate your success. I grabbed a section of my data and randomized the items and numbers so as not to disclose any real data but the fields are all the same and I tried to recreate using your directions and as far as I can tell the two workbooks are identical ------ BUT mine isn't working properly!!! It keeps coming up with a simple sum for the entire year of 2012 and 2011 instead of just the year to date data like you have created.
Could you please look at my workbook and tell me what I am missing here? If I had hair I would have pulled it out all over again this morning.
YTD My Try.twbx.zip 22.8 KB
1 of 1 people found this helpful
Right-click on [YTD Sales] > Edit table calc. Because this is a nested table calc, in the top of this dialog box, you'll see a pull-down for "Calculated Field." Select Week Max. Now you'll see the Compute using is set to Date, which means the table calc is operating on the Date dimension for each of the other dimensions in the view, in this case the Date Year dimension. So you're finding the Week Max for each year. This is why you're getting YTD total based on the max week in each year.
To fix this, in the compute using box, select Advanced and move Date Year over to the right-hand "Addressing" side. Nothing should be on the left-hand "Partitioning" side. Now you'll find the Max Week for all dimensions in the view.
As a side note, I notice the [YTD Sales] is computed over all dimensions, but is reset every Date Year. You could get the same result by just computing over "Date," which means all other dimensions will be used for partitioning. Since there is only one other partition in this view -- Date Year -- it was the same effect as putting all dimensions in the "Addressing" side and resetting every year.
Hope this is clear(er)!.
You are awesome, I knew there was something easy that I was just missing - and sure enough I was! Thank you so much for your help. This is exactly what I needed. I think I need to go learn more about partitions and table calculations. My understanding is fuzzy at best. Thanks again!