Problem with sort by expression

[wayne-a]

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)))))))

[rittermd]

Personally, I would create the buckets for your Dimension in the Load and get them out of the app.  Just more efficient.

Second, I would then put an inline table in that contains the values for the Diminsion and a value that will sort them in the order that you want. 

In your visualization you would reference the field in your inline table instead.  Just make sure that the field names match between the inline table and your other data.

This way you don't have all of these complicated nested Ifs in the app.

[Anonymous · Accepted solution]

I recommend you to use sort by your dimension (numeric), but make this dimension dual,e.g.

instead of 'After 1AM', use dual('After 1AM',1)

instead of 'Before 1AM', use dual('Before 1AM',2)

etc,

[wayne-a]

Thanks Michael that worked!  Much better than having another long expression in the sort.  Thanks again.

[wayne-a]

Thanks for the reply Mark.  Right now I'm using Michael's suggestion below but I may look into doing something in the load.  Thanks again for replying.