Thanks for feedback, Tom. I am sorry to say that I haven't made any progress on version 3.
I believe there is a good chance that someone knows how to do this and therefore recommend posting a question in Forums. The better the question is, the better the answer is likely to be.
Posting a Perfect Question shows various techniques that attract helpers.
Please ping me if you post a question, because I would for sure like to follow that thread.
Great, thanks for getting back to me, I'll let you know how I get on.
Head of Number Crunching
Direct Dial: 020 3141 8313
0845 459 9937
2 Exmoor Street
Show us some love on Facebook <https://www.facebook.com/Loafhome> /
/ Pinterest <http://www.pinterest.com/loafdotcom/> /
*"I couldn't be happier. I lead polar expeditions for a living and spend
several weeks a year in a sleeping bag in the minus forties, so having a
nice bed to come home to is something I appreciate more than most!" *said
Ben S after buying a kingsize Tight Space divan bed
*"I'm over the moon with our new bed. It looks great and we love the
mattress, it still feels like I'm staying in a 5 star hotel!" *Deirdre M
said after purchasing a kingsize Darcy bed in natural linen
*"I am happy, even very happy. I had a happy ordering and customer
service, a happy delivery and most of all I had a happy sleep because the
bed I purchased from you is everything that you said it would be." *e-mailed
Nathalie B after buying a double Coco French bed
Really Comfy Beds Ltd t/a Loaf is a UK registered company, number 6455834.
Registered address: Bridge House, Restmor Way, Wallington, Surrey SM6 7AH.
This email is confidential and intended for the use of the intended
recipient only. If you have received this email in error, please inform us
immediately and then delete it. Unless it specifically states otherwise,
this email does not form part of a contract.
kettan and Tom Scarr, this old thread inspired me to work on kettan's version 3, displaying projected revenue in-line with actual full year revenue. This is a great opportunity to explore table calculations, which I am not familiar with enough yet.
First, I'll describe how I displayed projections in-line with actuals. Then I'll discuss the formula I used for projections.
I used an IIF() function on table calculations to evaluate whether the record is the Current Year or not, so that for Current Year the record displays Projected Full Year Sales, while prior years display actual Sales.
IIF(Max(Year([Order Date]))=LOOKUP(Max(Year([Order Date])),FIRST())
, [Projected Sales Full Year]
The next trick is to display the YTD Sales in the foreground as an orange bar and Full Year Sales (projected for 2013, actual for prior years) as a blue bar in the background. Place Measure Names on the Color shelf and Measure Values on the Columns. Then remember to turn "Stack Marks" off. Go to Analysis > Stack Marks > Off. Make sure that the YTD marks are in the foreground by dragging the labels into the appropriate order within the Color legend.
It is important to note that I had to adjust Full Year Sales on the Measure Values shelf to Compute Using > Pane Down.
My projection assumes that the remainder of the year will have the same relationship to prior years as what was demonstrated in the year-to-date period compared to prior year-to-date periods. The calculation I used is commonly called a Response Curve in campaign response measurement.
I used table calculations to identify the average percent complete at 181 days into the year across the past three years (not weighted by total sales per year). Then I divided the current YTD sales by the average percent complete to project the full year sales.
Avg % of Total Sales:
((LOOKUP(ZN(SUM([Sales YTD])), 1)/LOOKUP(ZN(SUM([Sales])), 1))
+(LOOKUP(ZN(SUM([Sales YTD])), 2)/LOOKUP(ZN(SUM([Sales])), 2))
+(LOOKUP(ZN(SUM([Sales YTD])), 3)/LOOKUP(ZN(SUM([Sales])), 3))
Projected Sales Full Year:
SUM([Sales YTD]) / ([Avg % of Total])
I appreciate this post about managing the year-to-date period in a live data set: http://community.tableau.com/message/261409#261409, but I just used a hard coded date to keep it simple.
I created a dashboard that demonstrates the calculation steps and a picture of the Revenue Curve (% of total Sales by day).
I see your more recent post, but I used your older V2 workbook here.
kettan, thanks so much for posting this a year ago. It is inspirational because I hadn't been doing anything like this yet, but I will now. Also, I didn't realize that dayofyear was a datepart. Revolutionary! Thanks!
kettan, your old post lives on two years and counting!
I am attaching an updated workbook in Tableau 9.0. The great addition is a dynamic Year-to-Date period based on the max order date in the data set. As new orders come in, the max order date will inform the year-to-date period. A Level of Detail expression allows this.
I also added a detailed walk-through within the workbook to describe the steps and calculations. Please download this packaged workbook to explore. I am eager to see the additional features and efficiencies that the community builds on this workbook.
I also worked on a version that allowed the user to select the through-date for the year-to-date period via parameter, but I don't think that dynamic parameter defaults are available yet, so I wasn't able to have it default to the most recent order date. I have seen a workbook solve for this by using a parameter that isn't a calendar picker, but instead is a string list of Month + Day values and the default value is "Max Order Date," which would reference the current data through-date. I haven't tried it for myself yet, but there is some inspiration for you.
I presented this workbook the Kansas City TUG on April 29, 2015.
Here is an image of the final result compared to results from Tableau's built-in time series forecast. I was happy to find results fell within the confidence intervals. However, I don't expect these calculations to be as reliable when the year-to-date period is only a few days into the year! This exercise was more about demonstrating table calculations than sound projections.
Rebecca, at the moment we can't reliably include more than 1 image in a post. Your last image is broken. Reply to yourself, and post it again, so we can see it.
Thank you, Rebecca. This is wonderful. Getting a solution for this hard-to-crack-nut was a pleasant surprise.
I look forward to study your work. I am sure many others will too.
Great job Rebecca and kettan quite a useful post!
Hi Chris Hobson,
You mentioned to use MTD instead of YTD. Can you share the workbook? This is a very good example. Thank you very much Kettan.
I am trying to recreate YOY YTD Bar Comparision but I am unable to access cube dimension. But I didn't see any of my dimensions from my cube when I try to create new calculated field. Old solutions I saw mentioned about putting those dimensions in Level of Detail shelf but new version doesn't have LoD and I tried putting them under marks..but I still didn't see it.
I have no experience with cubes and therefore don't know how to help.
Hopefully someone else is able to explain why and share a workaround.
I think it is more likely to get help if you re-post this as a question in Forums.
I have not had success replicating this exercise from a cube data source. These table calcs require OLTP.
When using OLAP, you cannot use dimensions in a calculated field. This is a limitation based on the multi-dimensional nature of cubes.
If you're up to writing MDX (which I'm not ), then you can create calculated members.
Here's a good KB article about working with OLAP: Understanding Functional Differences Between OLAP and Relational Data Source Connections | Tableau Software
Here's an idea for allowing table calcs to reference OLAP dimensions: http://community.tableau.com/ideas/3281#comment-10603 (I noticed Johan has already back-linked it!)
Thank you kettan and Rebecca Sundquist for your insights. I decided to go with Microsoft SQL Server Table rather than using cubes with Microsoft Analysis Server. I just have to wait till calculated field option feature is supported in Cubes plus some of my users are Mac users and Cubes are not supported in Mac yet. Once I connect with Microsoft SQL Server, everything is working as expected.
This definitely helps.
This post changed my life!! Thank you!!