2 Replies Latest reply on Apr 4, 2013 7:36 AM by Adam Heckler

    Filter for Oldest N% of Items

    Adam Heckler

      I have a dataset that lists equipment and certain attributes of that equipment. One of those attributes is the original purchase date. Since we are on a 5 year refresh cycle, we want to determine which assets are the oldest 20% of our existing assets. The problem is that I'm having trouble creating a filter or something to do this in Tableau.

       

      Dataset looks approximately like this:

       

      Serial NumberBuildingPurchase Date
      1234567890Main Office1/1/2009
      2634768779Secondary Office5/6/2009
      8056348796Secondary Office3/5/2007
      2565756854Our Third Office5/6/2009
      6843849376Main Office3/5/2007
      4809989755Secondary Office5/6/2009
      2435363456Main Office1/1/2009
      2353768433Main Office10/12/2004
      2393873222Secondary Office1/1/2009
      3546578893Our Third Office3/5/2007

       

      How can I filter out the newest N% or oldest N% so that I can break the remaining assets down by building? I have tried several methods, but I'm relatively new to Tableau, so I have no idea if I'm on the right trail or not.

        • 1. Re: Filter for Oldest N% of Items
          Joshua Milligan

          Adam,

           

          I've attached a workbook that shows one way of doing this.  It includes commentary which I've included here as well.  Table calculations can be a bit tricky, especially if you are relatively new to Tableau, so feel free to ask if you have any questions.  I'd be happy to answer.

           

           

          Regards,

          Joshua

           

           

          Here are the steps I took:

           

           

          1. Create a calculated field using the Index() function that is computed using all the fields in the view and ordered along Purchase Date (right click the Index field on the Rows shelf and select "Edit Table Calculation" to see the setup).

           

          2. Create a calculated field based on the Size() function to give us the size of the window.

           

          3. Create another calculated field that divides Index by Window Size to give us the percent.

           

          4. Create a parameter to allow the end-user to control the percent.

           

          5. Create a new calculated field to compare the value of [Oldest %] to the parameter value and use this as the filter.

          1 of 1 people found this helpful
          • 2. Re: Filter for Oldest N% of Items
            Adam Heckler

            Thanks Joshua! This helped a lot!

             

            What I ended up doing (before I saw your response), was exporting the dataset to a CSV, sorting by the purchase date, taking the number of rows divided by 5, making a new column called "Quintile", and then putting a 1, 2, 3, 4, or 5 in the column as needed. Fortunately there were only 3000 or so rows, so it only took a couple minutes to do.

             

            While it isn't as flexible as what you showed me, it got the job done. I still learned a lot from your response! Cheers!