4 Replies Latest reply: Jul 17, 2017 10:33 AM by Wayne Antinore RSS

    Problem with sort by expression

    Wayne Antinore

      Hi, I have a set of data that I'm displaying as a bar chart.  The dimension is one of 6 time buckets that the event falls into for example "Before 6PM", "Before 9PM", etc.  I created a master item dimension for this and it works fine but by default it seems to sort based on size of the bar smallest to largest.  I want it to sort based on the time bucket in time order.  I've created an expression to do this but when I apply it it doesn't change the sort order.  If I add the expression as a dimension in a table I see the values calculated correctly so the expression itself seems fine, it just isn't applying for some reason.  Thanks in advance for any help.

       

      Definition of the dimension:

      =If ($(vDateReceived) < $(vDateVerified), (If($(vTimeVerified) > '00:59:59', 'After 1AM', 'Before 1AM')),

      (If($(vDateReceived) > $(vDateVerified),'Bad Data',

          (If($(vTimeVerified) < '18:00:00', 'Before 6PM',

              (If($(vTimeVerified)< '21:00:00', 'Before 9PM', 'Before 12AM')))))))

       

      Definition of the sort expression applied:

      =If (makedate(Year(Received),month(Received),day(Received)) < MakeDate(Year(Verified),month(Verified),day(Verified)), 5,

      (If(makedate(Year(Received),month(Received),day(Received)) > MakeDate(Year(Verified),month(Verified),day(Verified)),1,

          (If(MakeTime(hour(Verified),minute(Verified),second(Verified)) < '18:00:00', 2,

              (If(MakeTime(hour(Verified),minute(Verified),second(Verified))< '21:00:00', 3, 4)))))))