Yes pretty sure we can achieve this...Can you let me know what form your parameter takes? is is a string 'Q1 2016', 'Q2 2016'...etc. or are you just letting the user select the quarter, so Q1, Q2, Q3, Q4?...or something else?
The key here is to extract the Quarter number from this entry (which we can do in a multitude of ways!...for example if it was like my first guess INT(MID('Q1 2016',2,1)) would return the value 2)...once we have this, we can create a formula is the form of
IF DATEPART('quarter', [Date]) <= [Your Parameter Quater] THEN [Sales] ELSE 0 END
So this will give us a field that only contains values prior to the selected quarter for both years. If you bring your years and this field into the pane it will show YtD (to selected quarter) for any years in the viz. It's hard to be more precise, as I don't know what your final dashboard looks like, but hopefully the above (from a logic perspective) makes sense.
If not, or you can't get it working, can you mock something up in Superstore (or an anonymised version of your workbook) and I'll add the relevant calcs in.
Thank you for your reply and apologies for the delay in responding.
My parameter takes the form of '2016 Q1'.
The settings within the parameter itself use:
Data type: Date
Display format 2016 Q1
I use a range of values for 2015 and 2016 where quarter is my step size.
How would you write a formula to extract the quarter info in this instance? I think I can follow through the rest of the logic, sorry its all brand new to me :-)
Thanks for you help.
So if your parameter is a date-type then you can use the DATEPART function. This extracts just a part of the date (so the Quarter, Month...etc.).
So for example DATEPART('Month', #01/01/2015#) returns 1
as does DATEPART('Month', #01/01/2016#)
In this way you can use this to bring back just that month for any years. So if you have data for 2015 and 2016, and a parameter that was choose month (where the user had selected 3, aka March) then
IF DATEPART('month', [Date]) <= DATEPART('month', [Parameter Month Select]) THEN 1 ELSE 0 END
and bringing this field onto the filter shelf and setting to 1, would only return data from Jan - March for 2015 and 2016.
So similarly you can just DATEPART('quarter', [Your Parameter]) to extract just the Quarter Number.
If it had been a string, you could use the string functions RIGHT, LEFT, MID to get just part of it. For example LEFT('2016 Q2', 4) would return '2016' (the 4 right most characters).
Hope that does the trick, but let me know if not.
Thank you so much for your help. Rather than keep coming back to you on questions, I have taken your advice and packaged a workbook with the bare essentials to make this work. If you had the time I would very much appreciate your help.
Thanks again - I owe you a pint :-)
04.08.16~Tableau Mock Up.twbx 24.1 KB
Please find attached. So you needed to use DATEPART and not DATETRUNC (DATETRUNC has both the Year and TRUNC part, where as DATEPART brings out only that part). You also needed to apply the DATEPART to both the Date field and the Parameter.
In the attached I've created 2 filters, one for Selected Quarter, and one for Selected Quarter YtD.
Hopefully that makes sense, but please post back if not.
Thanks Simon, I am going to use that on my dashboard. I will use 2016 only for now and perhaps send back a screenshot of how I hope it will all look together - I may look at the 2015 cumulative totals and how to reset that filter as we roll back through the quarter.
Thanks again for your help.
Any chance you could have a look at the v.2? I thought I could make your original answer work, but if I could get the info appearing as 1 figure.
i.e. total accumulating for 2015, resetting to zero in 2016 and accumulating again based on use of the parameter.
I have included in the dashboard the figure I would hope to see based on what quarter is selected to help out.
So for a cumlative measure you need to use RUNNING_SUM (which Tableau has kindly put as a quick table calc option). In the attached I've shown this using the parameter, and just a regular filter (as you don't need to pull just the quarter part, but are looking as Date <= Selection you could just use a regular filter.
So to set up the running sum, we click on the total sales measure and select 'Running Total'
Running Total is a Table Calc, and so you can determine how it's calculated (i.e. over what dimensions). By Default Tableau sets this to Table Across, but we don't want that here (that would create a running sum across years for each quarter). We want to calculation to run table down, so we change that here
hope that helps, but please post back if that doesn't make sense.
99% there, I spent a few hours yesterday trying to display the running total as a "single value".
My dashboard has extremely limited space, so I can just about allow for the parameter to decide the quarter, and the running total applicable to that quarter to display as a single value. Is that possible? I couldn't see an option for this anywhere.
Thanks again Simon.
So if you just want the total, you can just remove the Quarter from the VizLoD...and once we do that we don't really need the running sum anymore. Tableau is all about the VizLoD...I think you might find my quora answer on the different calculation types and how they relate to the VizLoD useful Answer - Quora
Quick .gif below showing what you need to do...