How To Compare Current Ytd To Prior Ytd

I have an interesting relationship with engagement calculations. While they can be extremely frustrating for the nigh part, I observe the challenging ones then satisfying to effigy out. They are like lilliputian complicated puzzles packaged in what appears to be your boilerplate, everyday, mm/dd/yyyy format. At nevertheless, the types of calculations you tin can practice are so much more than than what meets the middle.

Comparison Measures for Unlike Time Periods

Recently I was putting together a dashboard with a mutual "twelvemonth to prior year" comparison where the most recent twelvemonth is a bar and the prior twelvemonth comparison is a reference line. Something quite like to Andy Kriebel's NHL Attendance viz.

So this got me thinking. I wanted me calculation to be dynamic. Often for static data sets y'all can but create a calculation that only returns data for a particular year or date.

Instance: SUM(IF [Engagement]=2017 And so [Sales] End)

I decided I wanted the flexibility in my dashboard to compare two numbers and that these would be dynamic based on the max date (YTD) of the information set. This was and then that I could simulate a regularly refreshing information set.

The electric current YTD sales to the prior complete yr sales: This would testify how something was trending to be above or below the prior twelvemonth.
The electric current YTD sales to the prior YTD at the same point in time (for instance, if the information was through April, then the calculation would compare this year's sales for January-Apr to final year's sales from Jan-Apr).

Max Yr & Prior Year Calculations

Using the Superstore data set within Tableau, I began to replicate some of these calculations. The first is isolating the maximum date in the data prepare. In order to really prove how this would work, I limited the data at the information source level to only through April 2018 so that I could simulate working with a fractional year.

So I created the following calculations:

Max Yr

{MAX(YEAR([Lodge Date]))}

This will return the maximum year in the data set regardless of the level of particular. Every row in the information fix will render the maximum year, or 2018 in this example.

YTD Sales

SUM(if YEAR([Order Appointment])=[Max Yr] then [Sales]ELSE 0 END)

This will return the Sales measure out as long as the year of the order date matches the maximum yr.

You tin meet from the tabular array below how these calculations piece of work.

The prior twelvemonth calculations are so done the aforementioned manner.

Prior Twelvemonth

{ MAX(Yr([Order Date])) }-1

Prior Year Sales

SUM(IFYEAR([Guild Date])=[Prior Twelvemonth] And then [Sales]ELSE 0 END)

With these four calculations you can create a max yr compared to prior year bar chart with a reference line, which works well for full years. If y'all had static full years and you wanted to compare you could also only fix the dates as I mentioned in a higher place. Just, I wanted something dynamic. I wanted to be able to compare the max twelvemonth to the prior year but also a YTD comparing. Because my information simply went through Apr, my bar chart looked like this below.

Prior YTD Calculations

In gild to create a prior YTD adding, I needed to be able to create a calculation that would but be truthful if the solar day/calendar month matches the maximum day/month in my information set.

Prior YTD Sales

SUM(IF Year([Order Appointment])=[Prior Year]
AND DATEPART('dayofyear',[Guild Date])<=DATEPART('dayofyear',{MAX([Club Date])})
THEN [Sales]
End)

This calculation will simply render every bit true if the order date was in the prior yr and the day of the year is equal to or less than the max 24-hour interval of yr for the entire data ready.

Setting Up a Parameter & Indicators

So, at present we have iii calculations we can utilize: The current/max YTD sales, the prior total yr sales, and the prior YTD sales.

I like to create charts that tin be dynamic based on the user and the question that user may accept. I'd rather accept ane chart that can practise two things than two charts if they don't need to be seen at the same time. Ultimately, these charts answer two different questions with the same fundamental data and nautical chart type.

In comes parameters. Parameters can help us switch between comparisons measures. In my example, I wanted to be able to flip between comparison the current/max YTD sales to either the prior full year or the prior YTD sales. I also wanted to add together in some sort of indicator that would help me assess where we were trending in relation to these 2 measures.

First, I created my parameter chosen "Compare to Value" that has two integer values: i for Prior Year to Appointment and 2 for Prior Year.

Secondly, I created some other calculation that would apply this parameter with the same proper noun.

Compare to Value

Example [Parameters].[Compare to Value]
WHEN i And so[Prior YTD Sales]
WHEN 2 THEN [Prior Year Sales]
END

Now that I have my dynamic comparative value, I tin create my chart. I used the current YTD every bit my main measure, with the comparison being the Compare to Value I just created above. The result looks similar this, though yous actually simply need Category on the Rows (the other pills are for the set action titles).

Comparison Indicators

Lastly, I created ii indicator values. The first is a color indicator that volition testify me if the current/max YTD sales are above or beneath the comparison value. When placed on the color mark the bars will turn a share of teal when the value is higher than concluding yr.

Increase in Sales

[YTD Sales]>[Compare to Value]

Since the Prior YTD Sales is likely to be above the YTD Sales, especially in the beginning of the twelvemonth, I as well wanted an indicator that would provide some estimate about the hereafter projection since the "Increase in Sales" color indicator is likely to be grey. While this is a "rough" estimate since information technology doesn't accept into account any seasonality in a data set, it volition provide a simple projection based on monthly boilerplate.

Basically, I wanted to exist able to say, there were 4 months so far in the information set and 4 divided by 12 is 33.3% so we should be approximately 33.three% of the style towards the full prior year'southward sales. If the sales is greater than that, then we are projected to be in a higher place last twelvemonth's sales.

What I decided to do was to create a calculation that gave me a percent of the current YTD to the prior full year and an indicator color that would just show when the comparison target in the parameter was for the prior total year, not the prior YTD since that would evidence up with the color indicator on the bars.

I created the new percentage adding as follows:

Percent to Compare Value

[YTD Sales]/[Compare to Value]

And so a Projection calculation that also required I count the number of months in order to accurately dissever by 12.

Count of Months

COUNTD(IF { MAX(Yr([Order Date])) }=Yr([Club Date])
So MONTH([Club Date]) End)

Project

IF [Parameters].[Compare to Value]=ii
AND [Percent to Compare Value]>([Count of Months]*(1/12))
And so "Projected Above Last Year"
ELSE "Projected Beneath Last Year"
End

The resulting viz looks like this, below, where you can see that fifty-fifty though when comparing the current YTD to the prior full yr for supplies, for example, sales is manifestly lower, I tin can now see in the pct to the prior yr that supplies is perchance projected to be college than final yr since after 4 months, information technology already had 48% of the of the sales from all of the prior twelvemonth.

Adding Set up Deportment

Just get to Lindsey Poulter's Tableau Public folio or blog. LOL. I directly got the ideas from her, then at that place is no sense explaining it here!

Here is the link to my terminal viz. I hope you find it useful!

Thank you,

Lindsay

Source: https://vizzendata.com/2019/02/03/fun-with-date-calculations-dynamic-ytd-and-prior-year-comparisons/