24 Replies Latest reply: Feb 6, 2018 2:20 AM by Mike Slottje

# Pivot Table Sorting - Qlik sense

Hi,

I Have ש pivot table with three dimensions and one expression,

the table is fully expanded and the third dimension shows only top 10.

I want that the third dimension will be in descending order by the expression.

any idea.

Thanks,

Jacob

• ###### Re: Pivot Table Sorting - Qlik sense

Go to the sort tab; go to ur 3rd dimension;

sort it by expression; tap the measure u're using and sort it as descending:

• ###### Re: Pivot Table Sorting - Qlik sense

Hi,

Thanks for the advise, unfortunately it isn't working.

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Jacob,

If the values in your dimensions can appear in different groups then the solution that Omar is showing may need some tweaking. Can you share a bit more about it?

Cheers,

Luis

• ###### Re: Pivot Table Sorting - Qlik sense

Yes, The third dimension can appear more than once for example

Shop   Group  Item     Qty

A          I           X          54

Y          58

Z          35

B          I           X          85

T           42

R          23

II          S          55

M         22

L          15

Thanks.

• ###### Re: Pivot Table Sorting - Qlik sense

Ok Jacob,

For the second and third dimension, please replace them with the following respectively:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)

That will make each value unique within each subgroup

I hope this helps,

Please try and let me know,

Cheers,

Luis

• ###### Re: Pivot Table Sorting - Qlik sense

Thanks, but still not working

you might forgot to mention: sum(qty) any where?

• ###### Re: Pivot Table Sorting - Qlik sense

Hi,

Yeap you're right. I'm assuming you're following Omar's recommendation (Sorting by expression with Sum, etc.)

It's the same that Omar said but the 2nd and 3rd dimensions on your Pivot Table needed to be changed

Please try and let me know

Cheers,

Luis

• ###### Re: Pivot Table Sorting - Qlik sense

Hi,

Tried without a success.

=AGGR(DUAL(Group,RowNo(sum(TOTAL Qty))),Shop,Group)

=AGGR(DUAL(Item,RowNo(sum(TOTAL Qty))),Shop,Group,Item)

• ###### Re: Pivot Table Sorting - Qlik sense

Hehe, not like that,

Is it possible for you to share data?

• ###### Re: Pivot Table Sorting - Qlik sense

I just created one with the data you posted

And the qvf..

Please give it a go and let me know,

Cheers,

Luis

• ###### Re: Pivot Table Sorting - Qlik sense

I think that I wasn't clear, my desire is that in each store + Group the items will be sorted by the quantity.

i.e

 Shop Group sum(Qty) A I 58 54 35 B I 85 42 23 II 55 22 15
• ###### Re: Pivot Table Sorting - Qlik sense

Hi, those items in the screenshot are sorted by the quantity for each shop and group.

I still don't get it

• ###### Re: Pivot Table Sorting - Qlik sense

Where are the items?

• ###### Re: Pivot Table Sorting - Qlik sense

like this?

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Jacob,

Please let me know if you still have this question

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

• ###### Re: Pivot Table Sorting - Qlik sense

Hi,

The solution you offered is working depending on the data set, in my case is not working well...

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Jacob,

When you created the post offered a requirement and some data, then I provided you with a solution that proved to solve the problem as you posted it.

I don't have any information about your data set, I only have the information you provided. If you want, please create another post with information that's closer to your situation.

As for this post, can you please close it?

Many thanks,

Luis

• ###### Re: Pivot Table Sorting - Qlik sense

I was searching all day for this solution and finally found it in the qvf file you've shared over here.

This saves me a weekend shift, so cheers to you mate

Mike

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Luis,

I have the same problem, I re-read the thread multiple times and it's not clear to me how to create the function.

Would it be possible to post a Qlik view examples (vs. Qlik Sence).

In my case I want to sort ascending the YoY sales delta for a given period (selected by the user) . The first level is the Salesmen - which is A->Z sort (no problem here); the second level is customer and third is part number, these two levels must be sorted ascending on the YoY delta.

I'm using below expression in the sort tab for the customer:

Aggr(Dual(%SoldTo,sum({\$<GLYear={\$(=(GLYear))}>} [Ext Price-F]) -  sum({\$<GLYear={\$(=(GLYear-1))}>} [Ext Price-F])),KeyAcctMgr,%SoldTo)

I'm using below expression in the sort tab for part number:

Aggr(Dual([Part Number],sum({\$<GLYear={\$(=(GLYear))}>} [Ext Price-F]) -  sum({\$<GLYear={\$(=(GLYear-1))}>} [Ext Price-F])),KeyAcctMgr,%SoldTo,[Part Number])

It seems that I also have to deal with some null results. I included AGGR(sum({\$<GLYear={\$(=(GLYear))}>} [Ext Price-F]) - sum({\$<GLYear={\$(=(GLYear-1))}>} [Ext Price-F]), KeyAcctMgr,%SoldTo,[Part Number]) on the table and seeing that in some cases the expression shows null numbers.

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Joaquin,

you should use the aggr(dual( function in your dimension tab. In the sorting tab you only need to use an expression like sum({\$<GLYear={\$(=(GLYear))}>} [Ext Price-F]) to sort ascending or descending based on your measure.

So your first dimension is salesmen.

Your second dimension will be "=AGGR(DUAL([Customer],RowNo(TOTAL)),Salesmen,[Customer])"

The third dimension will look like "=AGGR(DUAL([Number],RowNo(TOTAL)),Customer,[Number])"

Hope this helps!

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Mike,

Got it!

I'm almost there. However, I'm getting null values for the second and third dimensions.

I'm comparing Year-over-Year sales, the null value(s) is(are) for the customer that doesn't have sales in the selected year but have sales the prior year. The same applies to the third level, the part number when there are no sales on the selected year but there were sales the prior year.

See below extract from the table. I added the customer and number dimension on the table after dimension 3 to figure out what should be displayed instead of the null values. Note that all these records show no sales on 2017(Jan-Mar) but they display correctly for customers or parts with 2017 sales(Jan-Mar) ie. Part 10 and Part 11.

Joaquin

• ###### Re: Pivot Table Sorting - Qlik sense

Hi Joaquin,

it is a bit hard to say why this null value shows up, when I can't see the whole data set.

As far as I can see, it has something to do with your Y-o-Y sales expression.

What I would try first, is to make the same table with an expression showing 2017 sales, an expression showing 2016 sales and an expression with the delta between these two. In this way you can see if the null value has something to do with the dual(aggr( dimension or with the expression you're using.

To help you any further, it would be great to have an example file.