Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

27 Replies
joaquin66
Contributor II
Contributor II

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.

Thanks in advance for your help.

pablolabbe
Luminary Alumni
Luminary Alumni

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads 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

Anonymous
Not applicable
Author

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!

joaquin66
Contributor II
Contributor II

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.

Your help is appreciated.

Joaquin

Qlik-NullValues.JPG

Anonymous
Not applicable
Author

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.

Perhaps luismadriz can give us a bit more information?

canerkan
Partner - Creator III
Partner - Creator III

Hi Luis,

thank you very much for sharing your .qvf! I spent hours looking for a soluion and this is right what i was looking for!

DebbieL
Contributor
Contributor

After much searching I found this thread which solved my multidimensional pivot table sorting issue. Using the Aggr(dual function in my dimension tab and the Sum of payments in the sorting tab did the trick. However, when I view the pivot at the lowest dimension, some of the wording can be quite lengthy and the indentation pushes the wording even farther to the right. If the entire wording does not fit into the size of the column, it displays ####. If I manually adjust the column size to fit, then the chart becomes quite large.

I have tried the TEXT function (which cuts of the text but I can then hover to see the entire wordset) on the dimensions but this in turn causes the Payments sorting not to work. Any suggestions? This is Qlikview, not Qlik Sense.

Example.png

johndoub
Contributor III
Contributor III

when you change the order of the dimensions in the pivot table, do you have to change each of the AGGR expressions as shown below to reflect the new pivot order?

 

John Doub