2 Replies Latest reply: Aug 22, 2013 6:38 AM by Michael Solomon RSS

    Sort based on calculated dimention load order

    Christopher Clayford

      Good day

       

      I have created a calculated dimension based on the folowing criteria:

       

      =if(

      TRN_YM = 'MAR-2012' or

      TRN_YM = 'MAR-2013' or

      TRN_YM = 'APR-2012' or

      TRN_YM = 'APR-2013' or

      TRN_YM = 'MAY-2012' or

      TRN_YM = 'MAY-2013' or

      TRN_YM = 'JUN-2012' or

      TRN_YM = 'JUN-2013' or

      TRN_YM = 'JUL-2012' or

      TRN_YM = 'JUL-2013' or

      TRN_YM = 'AUG-2012' or

      TRN_YM = 'AUG-2013'

      ,TRN_YM )

       

      I need to know how to sort my data in the order specified above?

        • Re: Sort based on calculated dimention load order

          Hi,

           

          Put the below expression under sort by Expression:

          =Match(TRN_YM,'MAR-2012','MAR-2013','APR-2012','APR-2013','MAY-2012','MAY-2013','JUN-2012','JUN-2013','JUL-2012','JUL-2013','AUG-2012','AUG-2013')

          • Re: Sort based on calculated dimention load order
            Michael Solomon

            Another couple of method that may give you better performance is to solve this in the script.

             

            Method 1 is to generate a field in the script for the sort order. Create a field in either the same table as the TRN_YM field, or a separate linking table which has the TRN_YM and TRN_YM_SortOrder fields, where TRN_YM_SortOrder is an integer representing the sort order.  You could even have a 3rd field (TRN_YM_Required) which contains the IF statement above so it only contains the required limited months. Then in the chart you can use the TRN_YM_Required field as the dimension, and the TRN_YM_SortOrder as the Sort expression.

             

            Method 2 is to load the TRN_YM field into a temporary table in the order you want it presented, even if you have to hard code them in the specific order. This needs to be done before you load the field anywhere else in the script.  You can then drop the table later, but this will then allow you to sort by Load Order.