1 of 1 people found this helpful
I'd create a Date Range dimension that groups Current Year YTD dates, Previous Year YTD dates, and excludes all others:
IF [Order Date] <= Today() AND YEAR([Order Date]) = YEAR(TODAY())
THEN 'Current YTD'
ELSEIF [Order Date] <= DATEADD('year',-1,TODAY()) AND YEAR([Order Date]) = YEAR(TODAY())-1
THEN 'Previous YTD'
You can put this on the filter and exclude the "Exclude" Dates, then show values for a couple measures:
Thank you Jennifer! I think this could work for what i need.
One more question. How could I subtract Current YTD to Previous YTD with this formula?
To set up your table in the format you have it (Metrics as rows, two date periods along with a Difference and % Difference as columns) is not easy to do in Tableau (please, anyone else is the forum jump in and let us know if you know an easy way ).
One thing you could do is create separate measures for each Metric, Date Range, Difference (and I'm throwing % Difference in as well because most people want that). You can see that these are all calculated fields. You'd need to create 4 calculated field per metric you want to show. The table format will just be a straight line of metrics, rather than Rows of Metrics and Columns of Date Ranges with Dif and % Dif.
Sales C-YTD: IF [Date Range] = 'Current YTD' THEN [Sales] ELSE 0 END
Sales P-YTD: IF [Date Range] = 'Previous YTD' THEN [Sales] ELSE 0 END
Sales Difference: SUM([Sales C-YTD]) - SUM([Sales P-YTD])
Sales % Difference: ( SUM([Sales C-YTD])/SUM([Sales P-YTD]) ) - 1
You could force these metrics into a table-like format, but it's all extremely manual:
I've set up an Axis for each column: Row Headers (metric names), Current YTD, Prior YTD, Difference, % Difference. The Axis is a calculated field which is simply ATTR(1).
When you put this on the columns shelf, set its Marks Card to "Text" rather than "Automatic", right click the Axis and un-check Show Header to get rid of the Axis label and tick marks underneath.
For Axis 1 I created a Row Headers Calculation which is the names of my metrics separated by New Line (char(10)). Drag this onto Axis1's Text button.
Go into the Text Button and format the text (I left aligned it, and added to lines above so it will line up with the numbers we put in:
For Axis 2, I put in the "Current YTD" header, a blank line, then the CYTD Sales and CYTD Quantity. I left aligned them:
Rinse and repeat for the other three axes.
There are probably at least two other ways to get your data into this kind of table format that involve data restructuring. After the data restructuring and creation of calculations the setup may be less manual than above, but I don't have time to work them out right now, if interested I can write them out later:
1. Pivot your metrics so Metric Name is a dimension and Metric Value is a single column for all measures. The challenge with this is applying different formats in the same view ($, %, decimals, etc).
2. Maybe if we add records to the data which will be null but placeholders for Difference and % Difference, we could fill these with the appropriate calculations once in Tableau. Not sure about this, I'd have to work it out. We would need the two additional records to be repeated for every combination of dimensions in the data, and probably would have a nonsense date so we could recognize them (like 1/1/1000 and and 1/2/1000). Then we could create the Date Range as above with Current YTD, Previous YTD, Exclude, and the two new records would become Difference and % Difference into which I think we could use Lookup to get the right string of Metrics depending on Date Range field.
Hope this helps .
Hi John, just checking your query is now resolved?
Glad to hear it, Joe