topic Re: custom sort on calculated dimension in QlikView

custom sort on calculated dimension

Fri, 10 May 2013 20:37:13 GMT

Hi,

I have a calculated dimension that produces string names for the dimension. Its a fairly complicated calculation based on the data grouping various levels of data into the dimension called 'Professional', a different grouping into 'Consumer' and another one to group into 'Consumer'. I labeled the calculated dimension 'Products'. The results in that dimension are 'Professional', 'Consumer', 'Marketing'. The user wants that dimension sort to always be fixed at 'Professional', 'Consumer', 'Marketing' (so not alphabetical) I can not see how to do this. I am trying:

Match(Products,'Professional','Consumer','Marketing') but it still sorts it like this 'Consumer', 'Marketing', 'Professional'

Why won't the match function work? If it's better to do this differently in a load script, how would I do that?

Re: custom sort on calculated dimension

nagaiank — Fri, 10 May 2013 21:59:52 GMT

The following sort expression works for me.

=Match(Products,'Professional','Consumer','Market')

Re: custom sort on calculated dimension

Fri, 10 May 2013 23:33:08 GMT

Thank you. I realized I missed the = but it's still not working. It should come out in the order I typed them in the expression right? Professional first, then Consumer, then Marketing? I'm getting Consumer, Professional, Marketing which isn't even alppabetical but isn't the way I entered it either. Do you know what can be throwing this off?

Re: custom sort on calculated dimension

Fri, 10 May 2013 23:53:54 GMT

Also, when I do the same thing on a non-calculated dimension, it's working perfectly. In that case it's a true field name I'm referencing in the Match function. With the calculated dimension, I give the calculated dimension a 'label', and then that label is what I use in the Match function. Is that how I should be referencing it in Match? The only data being returned that I see are those 3 texts, so it should be doing what I'd hoped but it's not. Any thoughts?

Re: custom sort on calculated dimension

nagaiank — Sat, 11 May 2013 00:40:30 GMT

Did you check 'Sort By Expression' checkbox and uncheck everything else?

You may also try replacing 'Products' in the sort expression by the calculated dimension expression.

Re: custom sort on calculated dimension

Sat, 11 May 2013 05:06:04 GMT

Yes, everything else is unchecked. I tried your suggestion and put the expession in there instead of the name and I got the same results. Any other thoughts?

Re: custom sort on calculated dimension

Sat, 11 May 2013 05:42:20 GMT

I ended up adding the DUAL function in my calculated dimension formula, adding numerics, and then sorting by that numeric. That worked. Anyway, thank you for your input!!!