12 Replies Latest reply on May 24, 2017 5:58 AM by Mark Lowrie

    Rolling 12 months Date -- complex

    Mark Lowrie

      Hello all,

       

      I would like to do a rolling 12 months on this screen shot from April 2017 back to May 2016, but when I filter it down to that I do not want to lose the previous months from the visual. Is this possible in Tableau?

       

      I am not sure if it is a simple table calc, or if I need to create a few calculated fields. Let me know if I can be more clear.

       

      thanks,

       

      Mark

        • 1. Re: Rolling 12 months Date -- complex
          Joe Oppelt

          When you filter, use a table calc rather than a quick filter.  Table calc filter don't discard rows from the underlying table.  they just suppress nonconforming rows from showing on the viz.

          • 2. Re: Rolling 12 months Date -- complex
            Joe Oppelt

            So, for example, do:

             

            LOOKUP( (IF <whatever condition fits the dates I want to show> then 1 END),0 )

             

            This will create a value of 1 or null for all rows.  Put that calc on the filter shelf and select for value = 1.

            • 3. Re: Rolling 12 months Date -- complex
              Mark Lowrie

              I think this will work -- but struggling to find the correct way to write the function. So say I have a field of [Order Date] and want to show that over the last 12 months...what would be the lookup function you would write here?

              • 4. Re: Rolling 12 months Date -- complex
                Joe Oppelt

                Depending on what constitutes your most recent month ...

                 

                Let's assume you want to call May (the month of TODAY()) as the last month, and you want to look back to the beginning of June 2016 to get 12 months of data, then

                 

                DATETRUNC('month',TODAY())  ... will generate a value of 5/1/2017

                 

                DATEADD('month', -11, DATETRUNC('month',TODAY())  ... will generate a value of 6/1/2016

                 

                LOOKUP( (IF [your date field] >= DATEADD('month', -11, DATETRUNC('month',TODAY())  then 1 END),0 )

                 

                Will set a value of 1 for all rows between June 1, 2016 and today, and if that's what you put on your filter, you'll show the last 12 months on the sheet.

                 

                The underlying table will still remain intact, so the rolling 12 value for June 2016 will still add up the previous 12 months, but you will only be showing the marks from June 2016 through today.

                • 5. Re: Rolling 12 months Date -- complex
                  Joe Oppelt

                  The issue we run into when we use TODAY() like that is that you'll get a really "thin" value for the most recent month if you're early in the month.  Some shops want to display 12 months ending in the PRIOR month (so today, you would want to show from May 2016 through April 2017.)  In that case you'll create an end-date for the 12 month stretch.  And the do the DATEADD -11 based on the end date.  And determining the end-of-12-month calc is situational based on what your data looks like and what the logic of your business needs dictates.


                  In my shop the data source doesn't get updated until the close of the business month, so, for instance, the last date of data in my data source today is April 30, 2017.  I do:

                  { FIXED : MAX([Transaction Date]) }

                   

                  to grab the last date in the data source.  That's my end date, and then I take the steps I listed above, but do it against my FIXED calc instead of against TODAY().

                  1 of 1 people found this helpful
                  • 6. Re: Rolling 12 months Date -- complex
                    Mark Lowrie

                    First off, thank you for your timely responses. These are very helpful in my growth.

                     

                    While it definitely makes sense and you 100% understand my problem, I seem to be running into an issue with this logic. The date I was given is a Date Part and I do not think that works with the LookUp function. I keep getting an aggregation issue and I think it has to do with the created Date Part calculated field.

                     

                    I am a little stuck on this issue. I will try and recreate the logic you gave me and the time you put in is very much appreciated.

                     

                    Thanks

                    • 7. Re: Rolling 12 months Date -- complex
                      Joe Oppelt

                      If you provide a sample workbook, I could show you.

                       

                      Anonymize your Tableau Package Data for Sharing

                      • 8. Re: Rolling 12 months Date -- complex
                        Mark Lowrie

                        I have been a little busy. Here is a sample to my exactly data set that it is too sensitive to share Joe Oppelt

                         

                        Columns: I have a datepart with Month/Year and that same datepart is a filter which should be filtering out the most recently month.

                        Rows: I have a count of total people.

                         

                        From here a moving calculation was created to try and get the rolling 12 months. You can play around with this, but the end goal is to have:

                         

                        Only the last 12 months on the graph where when refreshed, it will only leave the last 12 months. However, we do not want to filter out the whole date range as that may be used for other calculations, if that makes sense.

                         

                        Let me know if you come to a solution on this.

                         

                        THANKS

                        • 9. Re: Rolling 12 months Date -- complex
                          Joe Oppelt

                          (V 10.0 here)  

                           

                          I have provided several ways to do what you need.

                           

                          Sheet 2 is a copy of Sheet 1.  Here I changed the format of the numeric [Order Date (copy)] so that you don't see the comma in the list.  I created a LOOKUP that lets you select which points you want to see.  Put it on FILTERS and did SHOW FILTER.  Keep chopping off points in the list of values and you will see that the remaining marks retain their displayed values.

                           

                          Sheet 3 follows the same principle, but I created a date field of Order Date that is formatted just to show month/year.  Look at [Order Date (Copy2)].  The datetrunc function truncates a date t the beginning of whatever period is specified.  Here I chose 'month', so all dates are changed to the first of the month in which the original [order date] value was.  Then I made another LOOKUP to look those dates up.  Notice that even though this is a date field, it lives with the MEASURES.  That's because it is a table calc.  It must be a measure.  I converted it to be a DISCRETE variable, and I put it on the filter shelf.  Same deal as Sheet 2.

                           

                          Sheet 4 takes a different approach.  I use INDEX to grab the last 12 INDEX values on the sheet.  INDEX() is a table calc, so the table-calc-as-filter principle holds here.  Every mark on a sheet (actually every one in the underlying table) has an index value.  We can tell it to address the order of index evaluation along various dimensions on a complicated sheet, but on this one, the default of TABLE(across) is right.  I do a WINDOW_MAX to grab the highest index value, and just count back 11 more to grab the last 12.

                           

                          Sheet 5 takes sheet 4 an extra step.  I have a parameter that lets the user select how many months he wants to see.  I incorporated that into a new filter like the one used in Sheet 4.

                          • 10. Re: Rolling 12 months Date -- complex
                            Mark Lowrie

                            Thank you for your time and effort, Joe. These are all GREAT solutions and very helpful to learn.

                             

                            While these are all excellent ways to filter or group 'x' amount of months together, I still have one other issue I am having. When we throw one of these into the filter, how can we get the whole data range to not filter down to only the selected months?

                             

                            For example, this user wants to still be able to see the whole range of February 2012 to December 2015, but only wants the data to be visible for the last 12 months.

                             

                            So, if Tableau had a hide button that would be the best interpretation of what I am looking for here.

                             

                            Perhaps these solutions will please the end user though

                             

                            Thanks!

                            • 11. Re: Rolling 12 months Date -- complex
                              Joe Oppelt

                              See Sheet 6.

                               

                              Instead of filtering the viz, I made a modified [Total People] calc that sets the value if the month fits the criteria, but leaves the value null if outside the range.

                               

                              To get the embedded calc that you see in there, open a new calc edit window and just drag the pill for the quick table calc from the ROWS shelf into the edit window.  The code that appears there is what Tableau is doing under the hood for that quick table calc.

                               

                              Now all months display, but a whole bunch of them are null.

                               

                              You could apply the same principle with the sheet2 or sheet3 method as well.  But given what I think you are aiming for in your actual workbook, using the INDEX method is probably cleaner.

                              • 12. Re: Rolling 12 months Date -- complex
                                Mark Lowrie

                                Thank you so much, Joe.

                                 

                                This is exactly what I was looking for. Fantastic!!!!!!