Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wayneantinore-a
Contributor

Problem with sort by expression

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

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Problem with sort by expression

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,

4 Replies
rittermd
Honored Contributor

Re: Problem with sort by expression

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.

mov
Esteemed Contributor III

Re: Problem with sort by expression

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,

wayneantinore-a
Contributor

Re: Problem with sort by expression

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

wayneantinore-a
Contributor

Re: Problem with sort by expression

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.

Community Browser