It's not a complete answer, but I think that the forward looking portion can be achieved by table calcs *in conjunction with* a join to a date table for future periods. I was (and still am) working on something similar for a net present value calculation example. I've attached that workbook. The thing to take note of is the join between one single row of data (bond data) and many rows of future date period data. This then allows you to start using table calcs to create "drift" or "upper/lower limits" that drift, etc...
Sorry it's not a grand slam answer; maybe when I've posted here 2038 times like Joe I will be more thorough.. (that's awesome btw, Joe)
npv.twbx 219.3 KB
The only way I can think of this is using the precalculated series and dual axis. When you use dual axis, you have an option of using multiple mark types which you can specify solid or dash lines.
As for calculating everything in tableau, it is going to be difficult if not impossible (I may be wrong here). I have tried this particular scenario earlier as well. I tested it by calculating a simple linear regression model inside tableau and get the slope and intercept. But when I tried to applied the model inside tableau, it complained that I mixed aggregate measures with non-aggregate measures.
Thanks Ty and Nopparit for the comments and suggestions. After playing with the data a bit more I think it will be best to calculate all projection scenarios ahead of time rather than over-complicate things by trying to use Tableau's table calcs. I expect some variant of Murphy's Law would otherwise apply: if I somehow managed to get a complex set of table calcs to actually work, someone will immediately change the projection requirements and send me back to square 1!
And, dropping the desire for dashed lines for the projections makes the viz absolutely trivial to produce, so I think that will be the best way to proceed at the moment.
In addition to your options, another method is to use a data blend with table calculations, the Measure Names/Values shelf and parameters to produce an interesting visualization.
In the attached, I have a few views, the first is the actual vs the window average (simple for demo purposes, but you can change this window avg to just about anything, want a liner regression instead, or a seasonal factor for certain months, or how about having the projection calculation being selected from a parameter, just about anything can be done). You can unpackage this workbook to see the structure of the underlying data. Basically I have one table that is just a list of months (the primary data source), and the other is the actual daily transaction level data that will be used to create projections.
The next sheet shows the month actual and projection values at the monthly level, and the third sheet is the view I interpret what you are looking for.
For both these sheets, marks are only being drawn where they need to be drawn, and the projections can be adjusted with the parameter controls.
Please let me know if this is what you are looking for, or if you have any questions about these calculations.
btw Ty, Tableau Software is awesome, and I love great questions like these. :) (Thank you for what you do in making software that I love to use)
actual_and_projection.twbx 32.2 KB
Joe, many thanks for this. I'll work my way through your (always fascinating) calculated fields to see if I can make sense of what you've done.
An interesting real-life complexity is that the projections for the current year are not going to be based on current-year data but rather on data from prior years. That's bound to add a fair bit of complexity to the calculations. It seems to me that means I can't use a filter to show just the data for the current year. Rather, some other means will be needed so that the data for all years is available for the formulae (which will have to have some year-specific aspects in the calculated field definitions so that ratios for each of the last five years can be generated).
Mark, all that you are looking for can be done with table calculations, likely a set of nested table calcs to combine different partitioning setups, and a table calc for filtering the display. With details on the logic desired, and sample representative data, and example can be made.
I've attached an Excel workbook that has a description of the required calculations, sample data, and sample charts. My sense is that the calculations involved are fairly complex and involve quite a few steps. Perhaps Tableau is smart enough to calculate everything in the appropriate sequence, so long as the calculated fields are carefully defined.
The workbook describes two possible approaches for the projection calculations. One approach is a basic "straight-line from this point to the end of the year" projection, while the second, more-detailed approach involves monthly computations. The latter would be considered ideal, but I expect the former is a more realistic approach.
sales_data.xls 4.5 MB
Thanks for great details, attached is basically my first pass at the calculations. I believe they are working as requested, but could be simplified with attention.
The sample data did not have very much variation in it, so you cannot really see that polylines are a different shape than the direct lines with out looking at the table of numbers.
Since you want to be able to add other dimension and have them partition the display, I chose a different route. Instead of using a data blend, I used the Hide option to allow Tableau to pad the data with a crosstab layout, and then hide it so it looks like a single view. Nearly all custom table calcs have their Compute using set to "Month", only the calcs with a ":" in their name use "Year" as their Compute using. (even though I have the default compute using set for each measure, sometimes it does not get set when placed, so all compute using settings should be checked when you place one of these pills)
In the calcs with the ";" in their name, you will see "LAST()-3", if you get an additional year of data, this would change to "LAST()-4" to make the range cover the previous 4 years, or you could just change it to "FIRST()". There are a few other situations like that were there are multiple methods to get the results, I basically just wrote it as I read your description, and upon review, I saw additional ways to accomplish the same results.
As for complexity, both your Basic and Detail were at about the same level of complexity, mostly a copy-paste after one was made. Your thoughts on weighting the years and other ideas are also very doable in Tableau. Once it is understood what is going on, and the steps that are taking place, I would not consider this unwieldy, it is just a bunch of nested table calcs, and could be consolidated.
projections.twbx 1.0 MB
Many thanks, Joe! I'm truly amazed at what you've been able to accomplish here. You've once again illustrated that table calcs are much more flexible than I ever imagined..
I'm expecting that there will be more variation between the basic and detailed approaches when I apply this to the real data, where there are several additional dimensions that should more clearly show the non-uniformity of the data throughout each year. In addition, real data is actually available for several additional prior years, some of which are known to have had more-significant variation in their monthly patterns, so modifying the calcs as you've noted should make the differences in the two approaches more noticeable.
Thanks for the tip re checking the Compute using settings. I still find it a bit strange that this important property of calculated fields isn't identified more directly as part of the calculated field's formula itself. As it stands, simply copying their formulae into a documentation dashboard or Word document isn't sufficient documentation. However, there are no doubt good explanations as to why things are done this way. Perhaps one day there will be some good third-party tools for fully documenting workbooks
I expect that when I examine all of these calculated fields' formulae I'll be scouring the forums for much more information on table calcs. Tableau is conducting a 'calculations' training session today, and hopefully it will include some material on them.
The fact that the Compute using settings are not identified directly as part of the calculated field's formula is both a key benefit in flexibility and represents a very different perspective on the concept of windowing/analytical functions than other applications. It does take time to understand all the factors that can effect the evaluation of the formula, and I am still learning something new about them nearly every day.
The main thing to be aware of is that the evaluation of table calcs is commonly based on both the layout of the worksheet and the options set in a dialog, in addition to the logic built into the formula. With all these factors, for most calculations there are multiple methods to accomplish the same results.
If you ever want to talk about table calculations, I welcome your email at: joemako [at] gmail [dot] com
Responding to an older post here as I have been reading up on using table calculations in Tableau to draw projections. I used the calculations on your YTD Actual and Projection tab to work with my own data in the attached.
I am having trouble getting my formulas to work and woudl love any advice you might have. My goal is to draw in projections based on the final actual point in the [volume] field. Similar to your YTD Actual and Projection example, I want to use the Window Average function to call the final point then multiply that point by 95% and finally add it to the running total.
So for example in the attached Client 2, Cohort C, the T5 [volume] point is 5. The running total at that point (T5) is 17. What I would like to do is draw in a second running total line that begins at T6 as follows:
T5: 17 (actual)
T6: 17 + (5*.95)
T7: 17+ (5*.95) + (5*.95)*.95
Any thoughts or advice you have woudl be much appreciate as I am just beginning to learn the Table Calculation functions. Thank you in advance for your time.
Projections.twbx.zip 19.5 KB
The thread is a couple of years old I know but I want to add some content to help others that will hit this thread in the future.
In a data set I encountered, both the historical data and projected data were reported in the same column of data. Only knowing that 2010 was the last historical reporting period separates historical and projected. I copied the one total column of data to a new column of data and called it historical. I changed the data to NULL for each year 2011 forward. The original total line of data is the projected data and contains data for all of the years. (I munged the data in R to transpose it so the rows and not the columns were the records.)
The two measures plot the same line through 2010. The historical data with the NULL for 2011 forward don't plot.
It's a total hack but works ok when there is just one projection line.
Joe's examples are very helpful and I've saved them to refer to when I encounter a projection problem that is more difficult than the one I solved. I will also refer to them when I start to learn how to use the WINDOW() functions.