This may be different from what you're asking but I've found info that could potentially help for showing two trend lines; one trend line for the overall data and one for a subset:
It is possible to display two trends lines, one for all the data and one for just an specific subset of the data by creating two graphs, one for each trend line then merging them with a dual axis.
A sample workbook demonstrating this with sheets 1-3 and the evolution of the steps is attached.
1. Create the desired graph and duplicate it by duplicating one field in the columns.
2. Create a set by right clicking on the field where it is desired to create the subset. In this case it is going to be the TOP 4 Sub-Categories. A detailed description on how to work with sets can be found in the following article:
Creating a Set
Use this set in color in one of the graphs.
You will see the data will be grouped into the Blue (Out of the Set) and the Red (IN)
The trick is to make the out color on the second graph White so it does not appear. Change the color of out to White.
Finally to merge both graphs into one right click on the field in Columns and select dual axis and then sincronize the axis.
It is possible to format the marks then, making then bigger or smaller to better visualize all of them.
More information about the Dual Axis and synchronizing axes feature can be found in the following article:
Trend line Tableau Sample.twbx 423.3 KB
Thanks Diego for the information. While it doesn't help my exact problem, it is an interesting concept and could prove to be useful.
I think I have a solution and an understanding of how to deal with missing data in a window_sum/lookup.
In the example you gave it appears you are using a moving calculation line. I believe this is just a “simple” example and you are hoping to apply your custom R12 Value table calculation (or the concept of it) to other areas, rather than the table calculation pane provided by Tableau.
IF you are trying to use a running average or a moving calculation and have missing data a way to deal with this is to understand the “period” you are evaluating and in the Tableau dimension pill there is an option to “Show missing values”. This method uses the period to drive what is seen and not seen over the view.
IF you are using this example as a proof of concept for window_sum/lookup. Your needs are a little more complex and you will have to write some conditional statements WITH IN the lookup to compare and inject a value. The “lookup” looks specifically at the data and the rows in the data so if you want to use this method you would have to prepare your data before it comes into Tableau. The reason your Location 1 example works is because you DO have data for both missing and added that covers the 12 month period.
I have attached a twbx with my table calc modification that shows the data table and the viz that compares R12 Value to sum(value) with table calc.
Hope this is helpful…
Example 471889 bc.twbx 136.0 KB
Thank you for the response and ideas. I haven't upgraded to 9.3 yet so I was unable to open your example. However, it ended up doing what I think your second option is, and that was to have the underlying source fill in the missing data. Luckily, I was able to craft the necessary query in SQL Server to fill in the missing data with 0.00 values for the measures for all of the possible combinations of dimensions. As a result, the solution ends up working. What is funny (in a sad way) is after all that work, we decided the view we were after really didn't help the story. Oh well. Learned a lot along the way! Again, then you for taking the time to help me.