ordering multiple field values

[ajlptnhs]

Hello,

I am currently creating a pick dim pivot table, in Qlik sense.

I have managed to bring in dimensions and measures. however, I want to be able to ordering the fields I am bringing in, in a particular order.

For example, I have the following fields picked :

Gender

Offence Group

Ethnicity Group

with the following values within the fields:

Gender: Female, Male

Offence Group: Drugs, theft, Robbery, Fraud

Ethnicity Group: Asian, Black, Mixed, White

I want to be able to order each field, in a particular order, usually alphabetically.

I've tried using wilmatch, like below:

wildMatch([Ethnicity Group],'White','Black/Black British',
'Asian/Asian British','Mixed','Arab or Other','Unknown/Not stated',

wildmatch([Gender],'Male','Female'
))

the ethnicity bit works, but the Gender bit of the formula, isn't changing.

Can anyone help, or have an alternative option I can use ?

as I have over 20+ fields to change the orders for.

Thanks

Ajay

[sunny_talwar]

I think it might be easy to help if you are able to share a sample where we can see the issue.

[ajlptnhs]

I am unsure how to provide a sample, due to Qlik sense being on my desktop.

If I attach the whole document, would you be able to view it ?

[sunny_talwar]

All you need to do is to attach the .qvf file.

[ajlptnhs]

thanks for replying, unfortunately due to company policy i'm unable to upload anything.

I have taken some screen shots, of what my issue is.

[sunny_talwar]

But Sex or [Race/Ethnicity Group (G)] is not your dimension? You have Dim as dimension, don't you?

[ajlptnhs]

yes, I am using pick dim as a dimensions, and having other [Sex], [Offence] and [ethnicity] as dimensions within the pick dim.

but the formula I have used in the sort expression, is only working for the first bit (sex), but not working for the (Ethnicity)

sorry, I hope that made sense?

[sunny_talwar]

Sorry, but I am not able to completely follow... would you be able to share the script for the Island table and then share all dimensions, and sort expressions from this chart as text (not image)?

[ajlptnhs]

Not a problem.

I have attached the script code, the dimensions, the measures and the sort expression I am using for pick dim (1)

I hope this helps you?

[sunny_talwar · Accepted solution]

Try this

=Match(Pick(Dim, [Sex], [Offence Group (Q)], [Race/Ethnicity Group (G)]), 'Male', 'Female', 'Intersex', 'Other', 'Prefer not to say', 'White', 'Black/Black British', 'Asian/Asian British', 'Mixed', 'Arab or Other', 'Unknown/Not stated')