Sort bar chart dimension by a related dimension not shown (IE sort items by item family)

[leenlart]

Hello!

I have a lovely bar chart showing the sell-in/sell-out difference by items.  The users would like the chart to be sorted by item type.  

If I add the item type, I can use it as a group, and then its very easy to sort by this, however, it reduces the readability of the graph.  (Our item family names are a bit long...)

I'd like to use a sort by expression on my Items to sort by the item type, but I can't seem to figure out the expression to use.  

I've tried this, but of course its not quite correct : 

=match(ITEM,[FAMILLE ITEM])

Any ideas ?

Thanks!

KL

[ajaykakkar93]

hi,

You can use wildmatch(FieldName,'Value 1','Value 2','Value 3'......)
the above is to be added in sort by expression in dimension & dimension is to be dragged on top of sorting i.e first sort is to be a dimension

[leenlart]

Hi ajaykakkar93,

Thanks for your reply, but the problem with this is that I would need to list the 30+ possible values.  They want to sort by a detailed level of the item family...  And then when the day comes that a new item family is added, the admin has to remember to update the expression for sorting.  

There must be a more dynamic way of doing this ?  (I hope!) 

Regards

[ajaykakkar93]

you can do that via the backend also, but yes sorting needs to be updated OR a master can be maintained for the same

[leenlart]

Could you be more explicit ?  I'm not sure I understand what you mean by "you can do that via the backend also".  

And could you explain what you mean by "a master can be maintained for the same" ?

Currently, the item family hierarchy is loaded from a database table (so the day they modify something in the ERP, its transparent in Qlik).  I receive a code and label for each level of each hierarchy type that we load into Qlik (item family with its 5 levels, item conservation type with its 2 levels, item brand...) .  Currently I concatenate the code and label and only keep this. 

My data model has an Invoices table, that is linked to the Items table by the item code.  The items table has the code and item label, then a field for each level of the different hierarchies.  

So since each item has one family entry,  I thought I could sort by the family easily.  ??

Thanks for your help.