3 Replies Latest reply on Jul 31, 2012 4:41 PM by Tracy Rodgers

    Create a date range that allows me view data from dates between today and set range

      I have sales data that organized by dates.  I want to be able to this data in 3 categories, one showing sales between today's date and the past 24 months.  The other showing the information from before the last 24 months, but not including the first range of information. and then another showing data from 24 months ago from today to 36 months from today.  I would like to be able to toggle between these views and see the list of sales information.  I would like it if the "today date" updated with the current date so my ranges reflect accurate information as time goes on.

       

      Does anyone know how I can set this type of filter up?

       

      Thank you,

       

      Nate

        • 1. Re: Create a date range that allows me view data from dates between today and set range
          Tracy Rodgers

          Hi Nate,

           

          I'm not sure that I completely understand the third category--24 months ago from today to 36 months from today.

           

          However, for the first two categories, two calculated fields can be created similar to the following:

           

          Today Minus 24 Months (this will show data prior to 24 months ago):

          if [Order Date]<=dateadd('month', -24, today()) then 'show' else 'hide' end

           

          Last 24 Months:

          if [Order Date]<=today() and [Order Date]>=dateadd('month', -24, today()) then 'show' else 'hide' end

           

          Then, create a string parameter that lists out these categories, and create another calculated field similar to the following:

           

          case [Date Range Parameter]

          when 'Previous to 24 Months' then [Today Minus 24 Months]

          when 'Last 24 Months' then [Last 24 Months]

          end

           

          Place this on the view. Right click on an instance of hide, and select Hide. Then, right click on the parameter and select Show Parameter Control.

           

          Hope this helps a bit!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Create a date range that allows me view data from dates between today and set range

            So I created my set of ranges using a formula similar to ones you outlined, but I have repeat data in my lists now.  So I have sales history data and each row is a separate transaction and I want to show a list of say just the customers  who most recent purchase was in the past 24 months.  and another list of customers where their most recent purchase was between 24-36 months. and then a final list of customers who have not made a purchase in the past 24 months.  ( I realize there will be will be overlap with list 2 and 3.

             

            so far i set up a formula to break down the date ranges but I dont know how to show the customer list without having duplicates for each transaction they made.  Right now in the last 24 month range, it is listing a customer 5 times if they made 5 purchases during that range, I only need them placed by their most recent purchase.

             

            Sale within past 24 months :

              IF DATEADD('month',-24,NOW()) <=[OrderDate] AND [OrderDate]<=NOW() THEN 'True' ELSE 'False' END

             

            Sale within 24-36:

            IF DATEADD('month',-24,NOW()) > [OrderDate] and[OrderDate]> DATEADD('month',-36,NOW()) THEN 'True' ELSE ' False' END

             

            Sale not within 24 months:

            IF DATEADD('month',-24,NOW()) >[OrderDate] THEN 'True' ELSE 'False' END

            • 3. Re: Create a date range that allows me view data from dates between today and set range
              Tracy Rodgers

              Hi Nate,

               

              If I understand correctly, you only want to see a list of the customer's last purchase date within the selected range. By creating another calculated field similar to the following should help you achieve this:

               

              if max([Order Date])=window_max(max([Order Date])) then 'true' else 'false' end

               

              Place your Customer then Order Date on the rows shelf. Then, place the above calculation to the right of these. Right click on the calculation and select Edit Table Calculation... Under Compute Using, select Advanced. Place your Customer field and then the Order Date field to the Compute Using box. Click OK. Choose Deepest for At the level and Customer for Restarting every. Click OK. Then, right click on the calculation again and select Filter and check only true.

               

              From here, you can remove the calculation if desired, and Order Date and Customer can be moved around.

               

              I have attached a sample workbook.

               

              Hope this helps!

               

              -Tracy