topic Re: sort order in straight table in QlikView

sort order in straight table

joshibabu_are — Fri, 02 Mar 2018 10:09:59 GMT

Hi Guru;s

I want to sort country column based on total employee count in all locations.

example :BU-API

Country - USA

employee count in 5 locations in us is less when compared to ITA(ITALY).

So ITA Should show first in Country column

US Head Count:431---For Business Unit-API

ITA head Count 505------For Business Unit-API

here pivot table not preferable because iam going to use this table in Nprinting.Nprinting wont support pivot table.

Please find the attached qlikview file

Thanks,

Joshi

Re: sort order in straight table

jerryyang756 — Fri, 02 Mar 2018 10:27:25 GMT

Add a expression as Sort Expression

AGGR(sum([2017 EOY Headcount]),Country,[Business Unit])

In sort Promote this expression to the top and Sort it in a Descending Order.

From Presentation tab Hide this expression.

Re: sort order in straight table

YoussefBelloum — Fri, 02 Mar 2018 10:33:13 GMT

Hi,

do you want to create the sorting based on the total employees count or on the total employees count for business unit API ??

because on the total without choosing a business unit, US should be Ranked N1

Re: sort order in straight table

joshibabu_are — Fri, 02 Mar 2018 10:51:42 GMT

i want to show based on actual headcount expression .

iam going to write this sort expression in Country sort expression

Thanks

Re: sort order in straight table

YoussefBelloum — Fri, 02 Mar 2018 10:58:09 GMT

Yes I understand that.

US is ranked 1 based on the GRAND TOTAL

and ITALY is ranked 1 base on the total by BU

if you're looking for the second option so take the solution provided above by Pritam

Re: sort order in straight table

joshibabu_are — Fri, 02 Mar 2018 11:12:56 GMT

hi Pritam Thanks for your time.

all the Business units should be one place after that DDA, next DP .....

how to achieve this

Thanks

Re: sort order in straight table

sunny_talwar — Fri, 02 Mar 2018 12:26:07 GMT

Is this what you are after?

Created an extra expression using this expression

Aggr(NODISTINCT Sum([Actual Headcount]), [Business Unit], Country)

Used it second in the sort order and hide the expression from the presentation tab.

Re: sort order in straight table

joshibabu_are — Fri, 02 Mar 2018 13:59:57 GMT

Iam working in qlik sense environment

any thoughts how to achieve same in Qlik sense

Re: sort order in straight table

sunny_talwar — Fri, 02 Mar 2018 14:02:02 GMT

Nice, you shared a qvw for qlik sense requirement ... Can you share the same thing in qvf to test out?

Re: sort order in straight table

joshibabu_are — Fri, 02 Mar 2018 14:15:17 GMT

same using in Qlik sense nothing change

Re: sort order in straight table

sunny_talwar — Fri, 02 Mar 2018 14:19:10 GMT

Looks better?

Updated: Use a calculated dimension like this

Dual(Country, Aggr(NODISTINCT Sum([Actial Headcount]), [Business Unit], Country))

Re: sort order in straight table

sunny_talwar — Fri, 02 Mar 2018 14:20:46 GMT

Not sure if you want me to delete the app and image once you have downloaded it. It seems that Location was scrambled in QlikView.... so that is why I ask

Re: sort order in straight table

joshibabu_are — Sat, 03 Mar 2018 11:56:52 GMT

yes please delete the app and image.

its working fine .thank you sunny.

can we achieve the same sort order with out calculated Dimension?

Iam using this created Table in Nprinting HTML Report ,here the issue is table showing correct result but

country column only showing grey background.

i am planning to post new thread any way

Thanks once again

Joshi

Re: sort order in straight table

sunny_talwar — Sat, 03 Mar 2018 15:56:28 GMT

Updated my post to remove the qvf and the image