4 Replies Latest reply on Feb 17, 2017 10:57 AM by Jason Cunningham

    Sorting Dates to most recent, 2nd to most recent and 3rd to most recent

    Jason Cunningham

      I would love some help with sorting dates.  I am trying to create a worksheet that sorts dates sequentially but displays the sequence in different columns.

      From a list that looks like this:

      I can easily create the first column with Max(Date)  but am having trouble setting up the 2nd and 3rd columns with calculated fields.

      Thank you!

      Jason

        • 1. Re: Sorting Dates to most recent, 2nd to most recent and 3rd to most recent
          Stephen Shepperd

          Hey Jason,

           

          See the attached workbook.

           

          What I did was create a ranking calculation using the following logic:

           

          RANK(MAX([Date]))

           

          This will give a ranked value of 1 starting with the most recent date and work down from there. I then made sure it was computing at the Date level and made it discrete.

           

           

          Throw Date onto text and that should provide you with the following image:

           

          This will prevent you from needing to create a brand new calc for every new 'most recent day' in case you want to show something like the 7th most recent day or 13th most recent day.

           

          Hope this helps!

          • 2. Re: Sorting Dates to most recent, 2nd to most recent and 3rd to most recent
            David Li

            Hi Jason, you can do this with LOD calcs. Here are the three formulas:

            { FIXED [Name]: MAX([Date]) }

             

            { FIXED [Name]: MAX(IIF([Date]<[Max Date], [Date], NULL)) }


            { FIXED [Name]: MAX(IIF([Date]<[2nd Most Recent], [Date], NULL)) }

            One of the main advantages of this method is that these three fields are all dimensions, so they're pretty flexible for later use.

            1 of 1 people found this helpful
            • 3. Re: Sorting Dates to most recent, 2nd to most recent and 3rd to most recent
              Jamieson Christian

              Jason,

               

              Attached is an example workbook (version 10.1.4) showing how to do this with Table Calculations.

               

              1. Create a calculated field for each of the "most recent" values you want to show. Each calculation will vary in terms of the range of the window used by WINDOW_MAX to find the appropriate date. Each calculation should be configured to compute along [Date], and sorted by Date (MAX, ascending).

               

               

              2. The calculations above will require that we put [Date] on the Detail shelf, but that will create a bunch of entries in our table. To compensate, create a calculated field [Index] to use as a filter. [Index] should be configured to compute along Date.

               

              3. Add [Index] to the Filters shelf, and filter on "At Most 1".

               

              4. Add the "most recent" fields to the Rows shelf, and [Date] (all values, discrete) to the Detail shelf, and you should have something like this:

               

               

              NOTE: There are a couple other ways of doing this. LOD Expressions are a bit more convenient for small quantities of "most recent" dates, but are potentially harder to maintain because of how each subsequent "most recent" expression must be crafted. You can also use scaffolding to enable any number of "most recent" values to be shown with only a single "most recent" table calculation; if you have to show quite a few, this is a much more appealing option than creating individual calculated fields for each iteration of "most recent".

               

              NOTE 2: If the same date can appear more than once for a single person, then that date will get reported multiple times in the "most recent" listing, when using this table calculation solution. If you specifically want each date to appear at most one time in the "most recent" listings, you may have to resort to more complex table calculations — or switch to an LOD Expression solution, which intrinsically "de-dupes" the dates.

               

              I hope this helps.

              1 of 1 people found this helpful
              • 4. Re: Sorting Dates to most recent, 2nd to most recent and 3rd to most recent
                Jason Cunningham

                Thank you Jamieson and David.

                Looks like all 3 answers work great.

                Fantastic to have both of your expertise.
                Thanks

                Jason