
1. Re: Create an average of the last 52 weeks, based on a custom date range length
Michael Someck Jul 10, 2017 1:50 PM (in response to Dermot Solon)Hi Dermot,
So I was able to come up with a solution. Though it may not be the most elegant, it does achieve what you are trying to do. Here are the steps I used (with a packaged workbook attached!).
Step 1: Calculate how many weeks away each date in your data is from your Start Date. I called this field [Which Period] and used formula DATEDIFF('week',[Date],[Start Date]).
Step 2: Calculate the length of the period you are looking at. I called this [Length of Period]: DATEDIFF('day',[Start Date],[End Date])
Step 3: Determine whether each data in your data is the start of one of the previous 52 periods. The criteria for this are: (1) the day of the week of [Date] is the same day of week of [Start Date], (2) [Date] is before [Start Date], and (3) [Date] is no more than 52 periods away from [Start Date]. Putting this all together, I created a boolean calculated field called [Period Start Date] to determine whether a given date should be the start date for a previous period:
Step 4: Create calculations for rolling sum and rolling average of engagements. For sum of engagements in periods, I used:
This is essentially saying sum engagements from the [Length of Period] days below up through the given date. I used something similar (but using WINDOW_AVG) for Average engagements in period.
Step 5 (optional): Calculate the end date for each period. Simple calculation was DATEADD('day',[MS_Length of Period],[Date]).
Step 6: Putting it all together. This is where it gets a bit tricky. If you don't do it in exactly this order, it might not work out correctly. In this order, drag the following fields to the Rows shelf: [Date], [End of Period], [Period Start Date]. Make sure you sort [Date] descending in order of data order (so the latest date, in this case Sept 30, 2017, is at the top). Add the two measures we created in Step 4 to text.
At this point, we are at the tab that I've titled "Before hiding". It won't look anything like what you want, since it's including a lot of "bad dates".
Step 7 (last step!): We want to hide everything that we don't want. This requires 2 steps. First, right click the word "False" anywhere it appears in the "Period Start Date" column and select "Hide". This will hide all dates that aren't 152 weeks before the Start Date chosen in your parameter, as well as dates that aren't the first date of previous periods. However, note that these are still being used in the running_sum and running_avg calculations.
Finally, we just want to hide that entire column of "True". To do this, right click on "Period Start Date" pill in the rows shelf and deselect "Show Header". The result should now be what you see in the tab titled "After Hiding" (I also added "Which Period" to the rows shelf so you can see how many weeks ago that period is).
I hope that wasn't too confusing. I've also attached a packaged workbook which should hopefully make things a bit easier. Please let me know if you have any questions
Michael

Tableau example_MS.twbx 301.1 KB


2. Re: Create an average of the last 52 weeks, based on a custom date range length
Michael Someck Jul 10, 2017 2:21 PM (in response to Michael Someck)I just realized that you are asking for something slightly different. You want the sum of the defined period and the average of the sums of previous periods. I misinterpreted and though you wanted the sum for each period and the daily average for each period.
I'll see if I can figure out a way to do that, too!

3. Re: Create an average of the last 52 weeks, based on a custom date range length
Dermot Solon Jul 17, 2017 9:26 AM (in response to Michael Someck)Thanks for taking a stab at it! I haven't been able to work out a solution and am starting to think it may not be possible... but I'll keep on trying out solutions and will post back if I work it out.

4. Re: Create an average of the last 52 weeks, based on a custom date range length
Michael Someck Jul 17, 2017 12:38 PM (in response to Dermot Solon)Hi Dermot,
So I've been thinking a bit more about this, and I've come up with another solution. This one isn't as complicated in terms of formulas and calculated fields, but it does get a bit messy in that it essentially creates 52 copies of your data. Not sure this is what you're looking for either, but figured I'd share anyway!
So the first thing I did was create an excel sheet with only one column titled "Period" that had 52 rows with values 152 (like this, except all the way down to 52):
Then, I created a "join" with your original data source. I use the word "join" loosely, since all it's really doing is duplicating your data 52 times and adding row "Period" to each duplication. In other words, there is one copy where Period=1, another copy where Period=2, etc.
To do this, right click your data source and click "Edit Data Source".
Then, under "Connections:, click "Add" and find the excel workbook you just created. It should automatically popup with the question of how you want to join the 2 data sources. The key here is to create a "dummy" join that will force the duplication I mentioned above. To do this, I chose the option "Create Join Calculation" and simply put 1 for each formula:
Once you do that for each sheet, you have the data source that you need (It shouldn't matter which kind of join you choose. I chose Inner).
The first thing you want to do is make sure that "Period" is a Dimension rather than a Measure. The rest is actually pretty simple and requires just 3 formulas. The first 2 mark calculate the beginning and end of each of the previous 52 periods.
START: DATEADD('day',7*[Period],[Start Date])
END: DATEADD('day',7*[Period],[End Date])
The final formula calculates the average of the sum of engagements for each period:
AVG({FIXED [Period]: SUM(if [Date]>=[START] and [Date]<=[END] then [Engagements] END)})
If you use this in your dashboard, you should get the desired outcome (Note that you also have to adjust your formula for Sum of Engagements Over Filtered Time Period to account for the duplication. One option is to divide by 52 because of the duplication. Another is to filter for just one value of Period in that sheet. I did the latter and filtered for Period=1 in that sheet.)
Again, this isn't that efficient since it's effectively making 52 copies of your data, but it does give the result you're looking for. Happy to answer any questions if this wasn't clear, but I hope this helps!
Michael

Tableau example_MS_v2.twbx 134.6 KB
