topic Re: Sorting within a dimension in a pivot table. in App Development

Sorting within a dimension in a pivot table.

amanjain57 — Sat, 16 Nov 2024 10:04:02 GMT

Hi,

I am trying to use a sorting function but due to the logic its unable to perform.

I will explain through a example.

Suppose I have two dimensions : Product Family and Customer.

Each product family have different customers. Customers in different product families could be the same.

Eg: Product Family : ABC, XYZ

Customer : Ford , Nissan

Measure : Sum (Revenue)

I create a pivot table which has Primary Sorting for Product Family by Revenue(descending order). The seconday sorting is for Customer again by Revenue(descending order).

Product Family Customer Revenue

ABC Ford $ 100

Nissan $ 200

XYZ Ford $200

Nissan $10

Total of ABC =$ 300, XYZ = $210.

So the sorting is Ok.

But what i want is that Ford and Nissan should also rearrange themselves in descending order in their product families.

According to the logic, it sums up the revenue for Ford =$300, Nissan=$210 and that is why Ford always appears above Nissan in product families.

How could we accomplish this task ??

So that in ABC the order is Nissan, then Ford. Xyz has the order Ford then Nissan.

Re: Sorting within a dimension in a pivot table.

Carlos_Reyes — Fri, 17 Feb 2017 00:05:18 GMT

As gwassenaar‌ explains in Is it possible to sort measure in pivot table using Qlik Sense?, you need to replace the dimension you want to sort by with a dual function. So... your first dimension will be ProductFamily but the second will be " Dual(Customer, Aggr(Sum(Revenue),ProductFamily,Customer)) ". Then you can sort the second dimension numerically in the sort tab.

Review the enclosed file to see the example.

Re: Sorting within a dimension in a pivot table.

shraddha_g — Fri, 17 Feb 2017 05:42:12 GMT

In Sorting Tab,

Go to Customer - Choose sort by Expression, use Revenue Expression there and apply descending order.

It works.

Re: Sorting within a dimension in a pivot table.

prat1507 — Fri, 17 Feb 2017 07:50:16 GMT

PFA the attached .qvw for your reference.

Regards

Pratyush

Re: Sorting within a dimension in a pivot table.

amanjain57 — Fri, 17 Feb 2017 13:35:14 GMT

Thanks a ton.

It works, but when i try to select something in the filter, the filter shows no value.

It should show me Ford GM etc.

Do you have any suggestions?

Re: Sorting within a dimension in a pivot table.

amanjain57 — Fri, 17 Feb 2017 13:35:46 GMT

Thanks Pratyush for the reply. Can you send me a qvf file.

This file doesnot runs on my Qlik Sense

Re: Sorting within a dimension in a pivot table.

amanjain57 — Fri, 17 Feb 2017 13:36:24 GMT

Hi Sharddha,

I tried that and simply sorting it out was not solving the problem.

Re: Sorting within a dimension in a pivot table.

amanjain57 — Fri, 17 Feb 2017 13:42:54 GMT

Also, when i just try Customer in the expression.

The filter works.

It only doesnot works with the dual expression.

Re: Sorting within a dimension in a pivot table.

amanjain57 — Fri, 17 Feb 2017 13:49:10 GMT

So, i got a way to run the QVW file in QVF,

But i could not see any sheet or any visualization.

Can you please send me the expression you used?

Re: Sorting within a dimension in a pivot table.

paolo_mapelli — Tue, 19 Dec 2017 14:43:37 GMT

It works unless you rearrange dimensions in pivot table (e.g. "Customer" before "Product Family")

Re: Sorting within a dimension in a pivot table.

johndoub — Thu, 07 Nov 2019 20:31:11 GMT

if i reverse the order of the pivot table do i have to change the expressions for each measure?