0 Replies Latest reply: Apr 12, 2013 3:35 AM by Dan I RSS

    Sort by expression

      Hello ,

       

      I've got an issue regarding custom sort in a bar chart . I've got a calculated dimension where i group a bunch of dates based on a specific interval and some expressions like Sum(Sales) and Sum(Cost).

       

      =if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-2) and date(MonthYear,'MM-YYYY')<=progMax

            ,date(AddMonths(progMax,0),'MMM-YY')&' - '&date(AddMonths(progMax,-2),'MMM-YY'),


                 if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-5) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-3)


                          ,date(AddMonths(progMax,-3),'MMM-YY')&' - '&date(AddMonths(progMax,-5),'MMM-YY'),


                                 if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-8) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-6)


      ,                                   date(AddMonths(progMax,-6),'MMM-YY')&' - '&date(AddMonths(progMax,-8),'MMM-YY')
                                                               )
                                                 )
                                        )

       

       

      progMax is a variable that reprezents the maximum date of MonthYear . If MonthYear is in the specified interval i will show the begining and the specified interval.

       

      The dimension works fine but I can't sort it ascending or descending. For example i'd like to sort it from "date(AddMonths(progMax,-6),'MMM-YY')&' - '&date(AddMonths(progMax,-8),'MMM-YY')" up to "date(AddMonths(progMax,0),'MMM-YY')&' - '&date(AddMonths(progMax,-2),'MMM-YY')".

       

      I've also tried Sort By Expression ascending or descending using the following code.

       

      =if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-2) and date(MonthYear,'MM-YYYY')<=progMax

      ,1


      if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-5) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-3)


      ,2


      if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-8) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-6)


      ,3)
      )
      )

       

      The sort by expression doesn't seem to work using this code.

       

      Any suggestions ?

       

      Thanks.