topic Re: Sorting not working in QlikView

Sorting not working

nikhilgarg — Wed, 20 May 2015 09:38:36 GMT

Hello,

I have a Pivot table with dimensions:

Role, Region, Department, Username and 2 expressions as Quantity and Amount.

I am implementing the Sorting on 2 expressions.

Under Sort tab , for each dimension i checked expression in descending and used :

pick(match(_SortBy, 'Quantities', 'Amounts'),

Quantity,

Amount

)

But Sorting is not working. Where am i wrong?

Thanks

Re: Sorting not working

amit_saini — Wed, 20 May 2015 09:43:47 GMT

Nikhil,

Try this:

Dual(Your_Field,Match(Your_Field, 'Quantities', 'Amounts'))

Thanks,
AS

Re: Sorting not working

nikhilgarg — Wed, 20 May 2015 09:50:41 GMT

Hey,Under Sort tab for each dimension i write expr as and selected descending:

Dual(Role,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(Region,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(Department,Match(_SortBy, 'Quantities', 'Amounts'))

Dual(UserName,Match(_SortBy, 'Quantities', 'Amounts'))

But still does not works

Re: Sorting not working

sunny_talwar — Wed, 20 May 2015 10:08:22 GMT

Would you be able to share a sample?

Re: Sorting not working

nikhilgarg — Wed, 20 May 2015 10:12:17 GMT

Plz found attached file . Also i am using personal edition so kindly letme know what changes i need to do here only.

Thanks

Re: Sorting not working

preminqlik — Wed, 20 May 2015 10:28:40 GMT

hi try this

pick(Match(_SortBy, 'Quantities', 'Amounts'),sum(Quantity),sum(Amount))

Re: Sorting not working

sunny_talwar — Wed, 20 May 2015 10:45:17 GMT

I think the problem is that this is a pivot table and it will need to break the pivot table's grouping to sort in the order you want.

But if you want you probably would be able to sort the way you want in a straight table

By using the following expression for just the UserName dimension:

=Pick(Match(_SortBy, 'Quantities', 'Amounts'), Sum(Quantity), Sum(Amount))

I hope this will help.

Best,

Sunny

Re: Sorting not working

nikhilgarg — Wed, 20 May 2015 10:48:51 GMT

Hey,

Thanks sunny but i want in pivot table. Cann't it be done in pivot anyway ?

Thanks

Re: Sorting not working

sunny_talwar — Wed, 20 May 2015 10:53:06 GMT

I may be wrong, but it seems that it will break the grouping. For example when you are sorting on Quantites your first and second row will be

Admin - India - Administration - O - 60

(Blank) - US - Administration - M - 56

Admin's are not all together anymore. If that's what you want, then you will end up seeing a straight table and I guess that's what the straight table is for.

So I doubt it is possible, but others may disagree

Best,

Sunny

Re: Sorting not working

nikhilgarg — Wed, 20 May 2015 11:02:59 GMT

The 1st row would be O-60

2nd would be C-6 (because it is in the same Role region and departent as of O)

3rd row would be M-56 and then the other values in the same department or role if any would get sorted

and so on.

Hope I'm clear

Thanks,

Nikhil

Re: Sorting not working

sunny_talwar — Wed, 20 May 2015 12:05:28 GMT

Don't think I know how to do it. Tried a lot of things, but did not get it to work. May be somebody else might have a better idea.

Best,

Sunny

Re: Sorting not working

Wed, 20 May 2015 12:16:08 GMT

Hi Nikhil,

you have a hierarchy of dimensions set, these will group accordingly in your pivot table, what you are talking about is splitting this table based on the bottom level username field on a row by row basis. Not something you'll be able to do in a pivot table I'm afraid.

You can order on the Y-value to give you an amount order, this will happen within each dimension though.

As Sunny has said, sounds like your after a straight table to be honest.

Joe