2 Replies Latest reply on Jul 14, 2015 12:56 PM by Joe Oppelt

    Continuous rolling 12 months interval while calculating accounts lost and gained?

    Tyler Lubben

      I have been asked to create a dashboard that shows the accounts lost and gained for a rolling 12 months period. The problem I cant solve is the rolling 12 months overlap each other so the data would need to be represented in multiple row of the crosstab, See the prints screen below and it will make more sense.

       

      My issue is defining these date ranges in Tableau and calculating the account lost and gained for these ranges. Accounts lost is 1 when the customer has no invoice date after 12 months. So the 12 month with no invoice date is an account lost. So if the 13 month falls within the date interval it will be an account lost for that interval. That account lost can be in multiple intervals since the intervals overlap. Account gain is when a new account is present in that date interval. It will show up it multiple date intervals. The data below is just a sample not using the data in the workbook.

       

       

      First Date of PeriodLast Day of PeriodAccounts LostAccounts Gained
      3/1/20102/28/201142151
      4/1/20103/31/201132154
      5/1/20104/30/201136201

       

       

      PLEASE HELP IF YOU CAN!   

        • 1. Re: Continuous rolling 12 months interval while calculating accounts lost and gained?
          Joe Oppelt

          See attached.

           

          Let's do this one step at a time.


          I have created several calcs you'll need to do a rolling 12.  I created a [beginning of range] and [end of range] calc that grabs 12 full months up to the last day of the previous month, based on the value of TODAY().  (You can adjust accordingly).


          But I can't do any viz for you because you have no current data in your date set.  As far as I can tell, it runs through some 2011 date.  (Maybe that's a function of the extract packaged workbook creation.)

           

          Values for start and end are displayed in your title.

           

          I created a [Date range to display] calc that you will use as a filter.  It will just bring in rows that fit the 12-month stretch.  Since no current data is in the data set, I can't test it.  But you would put that on your filter shelf and select for value = 1.

           

          I created a [Rolling 12 to Display] calc that will give you a sort algorithm to sort the last 12 months properly.  If you  look at the sort selection on the MONTH pill in the columns shelf, you'll see how I used it.

           

          I just pulled these from a rolling 12 viz I've done.  It all works the way I needed it to.


          Get some data for 2014 and 2015, put the [Date Range to Display] calc on the filter shelf (select for min and max value = 1),  and I think you'll get your last 12 months.

          • 2. Re: Continuous rolling 12 months interval while calculating accounts lost and gained?
            Joe Oppelt

            Get that to work, and we can take this logic to the next step you're looking to achieve.