9 Replies Latest reply: Mar 22, 2015 10:48 AM by si si RSS

    Sorting calculated dimension on Y axis

      Hello,

       

      I have created a calculated dimension for my bar chart as an If statement:

       

      =

      if(eB_DocDatePlanned >= Today() and eB_DocDatePlanned <= Today()+30,'+30 days',
      if(eB_DocDatePlanned >= Today()+31 and eB_DocDatePlanned <= Today()+60,'+31 to 60 days',
      if(eB_DocDatePlanned >= Today()+61 and eB_DocDatePlanned <= Today()+90,'+61 to 90 days',
      if(eB_DocDatePlanned >= Today()+91 and eB_DocDatePlanned <= Today()+120,'+91 to 120 days',
      if(eB_DocDatePlanned < Today() and eB_DocDatePlanned >= Today()-30,'-30 days',
      if(eB_DocDatePlanned <= Today()-31 and eB_DocDatePlanned >= Today()-60,'-31 to 60 days',
      if(eB_DocDatePlanned <= Today()-61 and eB_DocDatePlanned >= Today()-90,'-61 to 90 days',
      if(eB_DocDatePlanned <= Today()-91 and eB_DocDatePlanned >= Today()-120,'-91 to 120 days'))))))))

       

      I am now having a difficult time sorting the dimension.

       

      Sorting by Numeric Value, ascending, I see the following order:

      -31 to 60 days; -61 to 90 days; -91 to 120 days; -30 days; +30 days; +91 to 120 days; +31 to 60 days; +61 to 90 days

       

      Sorting by Text, A -> Z, I see the following order:

      -30 days; -31 to 60 days; -61 to 90 days; -91 to 120 days; +30 days; +31 to 60 days; +61 to 90 days; +91 to 120 days

       

      Any ideas on how to get the correct order:

      -91 to 120 days; -61 to 90 days; -31 to 60 days; -30 days; +30 days; +31 to 60 days; +61 to 90 days; +91 to 120 days

       

      Thanks for your input!