topic Re: sorting order in QlikView

sorting order

upaliwije — Fri, 08 Sep 2017 10:08:10 GMT

Hi FRiends

I have following pivot table in my QV doc'

I have derived this TOTAL rows with the help of following calculated dimension

=Pick(Dim, BRANCH, 'TOTAL')

I have sorted branch field with 'Branch No' in descending order since I want to TOTAL row come to the bottom. However due to this my branch order is shown in descending whereas I want it to be shown in Ascending order with Branch1 being shown in 1st row and Total row to remain as the last row . Can you please advise me how to overcome this issue.

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 10:12:49 GMT

Go to sort tab , for Branch dimension, in Expression (ascending) use below expression

=Pick(Dim, keepchar(BRANCH,'0123456789'), 100)

Re: sorting order

jmvilaplanap — Fri, 08 Sep 2017 10:13:48 GMT

Hi Upali

Maybe you can create a DUAL field with Branch (How to use- Dual()) it will be easy order it later.

Regards

Re: sorting order

upaliwije — Fri, 08 Sep 2017 10:25:03 GMT

Hi Kushal

I have done it but it is not working. Now the TOTAL come in between Branches

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 10:27:27 GMT

make sure you just have expression sort, any other sort like text, numeric, remove that

Re: sorting order

upaliwije — Fri, 08 Sep 2017 11:33:15 GMT

This is how I did it

Still the same result

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 11:44:13 GMT

Remove '=' After branch, due to synatx error it is not showing

=Pick(Dim, keepchar(BRANCH =,'0123456789'), 100) -> wrong

=Pick(Dim, keepchar(BRANCH,'0123456789'), 100) -> Correct

Re: sorting order

upaliwije — Fri, 08 Sep 2017 11:58:09 GMT

Sorry Kushal

I spotted the error after adding my screen shot and corrected the same , still the report is not sorted the way I want . Now the Total Comes to bottom but other branches not ordered in numerical order.

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 12:04:36 GMT

Would you be able to share the sample?

Re: sorting order

upaliwije — Fri, 08 Sep 2017 12:19:44 GMT

This is my sample Qv doc. pls help

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 12:27:57 GMT

try to sort it by NO

=Pick(Dim,NO, 1000)

Re: sorting order

upaliwije — Fri, 08 Sep 2017 12:38:56 GMT

Thanks Kushal

It works. Could you explain me how it works please

Re: sorting order

Kushal_Chawda — Fri, 08 Sep 2017 12:56:48 GMT

In Pick function Pick(Dim, BRANCH, 'TOTAL'), you have two dimension BRANCH and Total. where Total is static

so to sort this dimension Pick(Dim, BRANCH, 'TOTAL') we should have some number associated with it. For Branch we have NO field which has numerical values. For total I assigned static value 1000. I assigned 1000 here because Branch NO has 3 digit values, so number for Total should be greater than Branch NO so that Total can appear last in ascending sort order

So, in this expression Pick(Dim,NO, 1000), NO sorts the Branch and 1000 is greater number which assigns to total.

Re: sorting order

upaliwije — Fri, 08 Sep 2017 13:28:36 GMT

Thanks,

Really very helpful