So hows this for you?
So first I create a calculation to work out the last month of my data
Then I use this to create a Month Index
DATEDIFF('month',[Order Date],[Max Date])*-1
so current month is 0, then -1, -2..etc.
I can then use this to create my Rolling Year slices
[Rolling 12 Month Classification]
IF [Month Index] > -12 THEN 'This Year Rolling 12 M'
ELSEIF [Month Index] > -24 THEN 'Last Year Rolling 12 M'
and then one final bit is to allow me to plot both Decembers on the same axis...
[Month Index - Normalised]
IF [Month Index] > -12 THEN [Month Index]
ELSEIF [Month Index] > -24 THEN [Month Index]+12
and then with a bit of dragging and dropping (and a filter on weeks <=-24...)...which is fully dynamic when data updates
on the attached, the 'Working' tab shows you what everything is doing.
Hope that helps, and makes sense but let me know if not
Thanks for your efforts and I almost thought you had cracked it when I saw the month index. Alas, it's not *quite* what I wanted.
Your calculation beautifully provides a 12m rolling window say jan 14 - dec 14/ jan 15 - dec 15/ jan 16 - dec 16 etc.
What I wanted to see was jan14-dec14/ feb14-jan15/ mar14-feb15 . Do you see?
So the end result would say 'jan16' (which would look at data from jan16 and the previous 11 months), next to 'feb16' (which would look at data in feb16 and the previous 11 months).
Do you have any ideas to go one further and crack this behemoth?
ah that's a shame...it's still a handy trick!!
How about this one?
here I've used WINDOW_SUM from the current to 11 partitions back. The image above shows the first point (orange) is the addition of the previous 11 (and it does the same for the next month, and so on). I the final Viz I just "hide" the previous period (as Tableau needs access to this to calculate the rolling sum, so we can't filter it out)
The way I handle Rolling Months is with a table calculation and then indexing the months to get my desired view.
If you could share with us a .twbx file that would be great. That would really help us help you.
But I've attached a solution, and it'd be tricky without talking to you or showing you with your own data.
Here is the Index Month calc:
Here is the rolling distinct count of order id:
-I then add Months and Years to the view.
-add the Rolling calc and Index to the view.
-then put index on the view and compute using Pane Across
- filter the Index of 0 so it shows the last month. The trick is that this last "Month" actually has 12 months of data rolled inside of it.
Hope that helps, and if you need more explanation i would gladly explain.
EDIT: After seeing your reply to the other suggested solution, you may come out with the same "Not Quite" answer.
Book for Vivian.twbx 1.2 MB
I'm afraid I can't share due to compliance issues. That's no concern though as you both appear to understand what I want now. I had already tried the table calculation and it works perfectly for, say number of jobs or sales. if we had 2000 jobs in jan and 2500 jobs in feb, then the number of jobs in the period jan-feb is the addition- 4500.
However, with members, if 2000 members used us in jan and 2500 used us in feb, the number of members that used us in the period jan to feb is not necessarily 4500 as many members have used us in jan and feb and we don't want to double count them. So the actual number we want is going to be a fair bit less than the simple addition of the two.
It seems like I'd need some kind of dynamic filter that adjusts according to the displayed month (i.e. brief date > 'displayed month' - 12). But I don't know how to do this!
1 of 1 people found this helpful
Vivan, you're trying to count distinct Users over moving window
where repeating occurrences of Users may exist.
There is an epic thread on the very topic here:
I intentionally point you to my (latest) answer,
but reading the whole thread is definitely recommended.
If you have any questions after reading / applying, please ask.
Thanks for the help but I can't make it work. I've attached a workbook where I've added all the same formulas but for some reason I end up with a list of member ID's and the same number next to each one. Can you take a look?
tableau forum help.twbx 713.1 KB
1 of 1 people found this helpful
You're likely to set the [Is first: True]
Table Calculation on Filters
to Compute using [Member ID].
Doing this, you've got a single Cell
for each Year/Month on the view.
Hope this could help.
Oh man, this is so complex I'm not even going to try to understand!
Thanks though, that worked. Finally!!
Yuri-ly good at this! See what I did there?
And thanks to the other folks who took a stab at this behemoth on my behalf.
Your solution was useful for one of my requirement. However have few other issues.. Please advice.
1. Instead of showing 0 to -11, How do I show it from Dec to Jan(If max(order date) is Sep 15 , then Sep to Aug and so on)
2. How do I calculate the % difference between Latest Year Rolling 12 M & This Year Rolling 12M.
3. Color coding. If % diff between previous month & current month is >=0 then blue else red.
So we can add this calculated field in
DATE(IF [Month Index] > -12 THEN [Order Date]
ELSE DATEADD('month',12,[Order Date])
which we can then use for the Axis, rather than the month index.
To get the YoY we can use the quick table calculation of Percent Difference
and set it like this
We can then bring a copy of this onto the colour tile (if you hold CTRL and drag the pill from the rows shelf to the colour tile, then you'll get a new instance of the pill on the colour tile).
Hope that makes sense, and does the job!