11 Replies Latest reply on Jul 20, 2018 3:49 AM by Simon Runc Branched to a new discussion.

    Rolling 12 month window

    Vivian Perceval

      Hi,

       

      I have a database of members and the jobs they request. Finding out how many members are requesting jobs per month is easy enough- I use a countd(memberID) and put request month in to columns.

      But I'm struggling to work out how to display how many members have made a request within the last 12 months. So Jul 2015 - Jun 2016, Aug 2015 - Jul 2016 etc etc.

      Obviously I can do this one by one by changing the filter but what if I want to see how it changes month by month in one view?

       

      Hope that's clear, ask me questions if not..

       

      Many thanks

        • 1. Re: Rolling 12 month window
          Simon Runc

          hi Vivian,

           

          So hows this for you?

           

          So first I create a calculation to work out the last month of my data

          [Max Date]

          {MAX([Order Date])}

           

          Then I use this to create a Month Index

          [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'

          END

           

          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

          END

           

           

          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

          • 2. Re: Rolling 12 month window
            Vivian Perceval

            Hi,

             

            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?

            • 3. Re: Rolling 12 month window
              Simon Runc

              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)

              • 4. Re: Rolling 12 month window
                Matthew Risley

                Vivian,

                 

                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. 

                • 5. Re: Rolling 12 month window
                  Vivian Perceval

                  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!

                  • 6. Re: Rolling 12 month window
                    Yuriy Fal

                    Hi guys,

                     

                    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:

                    Re: How to count distinct users on a running period

                     

                    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.

                     

                    Yours,

                    Yuri

                    1 of 1 people found this helpful
                    • 7. Re: Rolling 12 month window
                      Vivian Perceval

                      Hi,

                       

                      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?

                       

                      Thanks

                      • 8. Re: Rolling 12 month window
                        Yuriy Fal

                        Hi Vivian,

                         

                        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.

                         

                        Yours,

                        Yuri

                        1 of 1 people found this helpful
                        • 9. Re: Rolling 12 month window
                          Vivian Perceval

                          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.

                          • 10. Re: Rolling 12 month window
                            Ashok Ramineni

                            Hi Simon,

                             

                            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.

                             

                            Thanks!

                            • 11. Re: Rolling 12 month window
                              Simon Runc

                              hi Ashok,

                               

                              So we can add this calculated field in

                               

                              [Date Label]

                              DATE(IF [Month Index] > -12 THEN [Order Date]

                              ELSE DATEADD('month',12,[Order Date])

                              END)

                               

                              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!