1 Reply Latest reply on Oct 18, 2016 3:17 AM by ra.sae.0

    Date Sorting

    Toh Wangting

      I have a list of date labels (as shown below), is there any way for me to sort the labels such that the date labels will be automatically displayed in the order FY2013 Plan, FY2014 Plan, FY2015 Plan, FY2016 Plan, FY2016 YTD Plan YTD (month name), FY2016 YTD Act (month name) ? I know i can sort it manually, however if i were to add in new data for other months (e.g. September), i will have to manually update the sorting myself once again, but i would like it to be done automatically.

        • 1. Re: Date Sorting
          ra.sae.0

          Hi Toh,

           

          Please see to the attached workbook and the below formula.

           

          Create a Calculated Field and place it as your first dimension in your view.

          Make it hidden, i.e right click untick the Show Header.

           

          Hope this helps.

           

          If you have month as a Number in your data, pls replace the below long IF statement for Month with it.

           

           

          str([Year No])+

           

           

           

           

          str(

          if [Month] = "January" then

          1

          elseif [Month] = "Februray" then

          2

          elseif [Month] = "March" then

          3

          elseif [Month] = "April" then

          4

          elseif [Month] = "May" then

          5

          elseif [Month] = "June" then

          6

          elseif [Month] = "July" then

          7

          elseif [Month] = "August" then

          8

          elseif [Month] = "September" then

          9

          elseif [Month] = "October" then

          10

          elseif [Month] = "November" then

          11

          elseif [Month] = "December" then

          12

          ELSE

          0

          END

          )

          +

          str(

          if CONTAINS([Year],"Plan") then

          1

          ELSE

          2

          END

          )