3 Replies Latest reply: Dec 18, 2013 11:09 AM by Nicole Smith RSS

    Help with chart sorting problem

      I am having problems with a chart not sorting correctly when I group the dates into six month segments.

       

      My bar chart is just a simple count of activity split out into the various months (ranging from Jun 2008 to Dec 2013). The users require an option to view the data split by individual month (Jun 08, Jul 08, Aug 08, etc) or by six month periods (Jan-Jun 08, Jul-Dec 08, Jan-Jul 09, etc). I have used a variable to create this option, and have incorporated it into the dimension of my bar chart like so:

       

      =IF(vTime='Six',

      IF(Month([Date])<=6,'Jan-Jun','Jul-Dec')&' '&Year([Date]),

      IF(vTime='One',

      Date(MakeDate(Year([Date]),Month([Date]),1),'MMM-YY'),

      null()))

       

      The Dimension above is named "Month", and is the only dimension in the chart. On the sort tab, Month is sorted by Numeric Value Ascending.

       

      When the axis is single months, the chart sorts correctly (as shown below):

      OneMonth.JPG.jpg

      But when the axis becomes six month periods, the bar for Jan-Jun 2008 moves towards the middle and out of sync with the rest of the bars. All other bars are in the correct order, apart from this one (see below):

      SixMonth.JPG.jpg

      I have tried copying the dimension expression into the sort expression to get it to work, but no success. I have also manipulated the dimension formula in the sort bar to convert Jan-Jun 2008 to 2008.1 and Jul-Dec 2008 to 2008.2, and that has not worked.


      I am hoping somebody on the community can help with this, because I have tried the most logical things and still this small bar is in the wrong place!

      There is nothing I can think of