Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
gloria_bertini
Creator
Creator

Problem to sort by measures in a pivot table

Good evening to all,

First of all sorry for my english.

I have a problem to sort data in a pivot table with four dimensions and two measures.

I have to sort value on one of the measures.

I set ‘sort by espression’ but it works only for the first dimension of the pivot, the external one.

I tried with different expression but nothig works.

I Actually use the function:

Aggr(Max(Aggr(Sum({<TipoOperazione = {'7'}, Causale = >}QuantitaFinale)/Sum({<TipoOperazione = {'7'}>} total QuantitaFinale),Articolo )),Articolo)

Where Articolo is the dimension and

Sum({<TipoOperazione = {'7'}, Causale = >}QuantitaFinale)/Sum({1}{<TipoOperazione = {'7'}>} total QuantitaFinale) is the measure

Do you have any suggestion?

Thanks in advice

Kind regards

19 Replies
timpoismans
Specialist
Specialist

Hi Gloria

 

I've discovered what the issue is, but haven't been able to solve it.

When sorting by expression, the sorting fails when you try to sort [GruppoMerceologico].  It seems the reason why it fails is because it calculates the expression on [GruppoMerceologico], not on [Magazinno] -> [GruppoMerceologico]. So the sorting is showing is the sorting you'd see if you'd take a normal table, use [GruppoMerceologico] as dimension and 

Sum({<TipoOperazione = {'7'}>}QuantitaFinale)/Sum({<TipoOperazione = {'I','S'}- {'C'}>}QuantitaFinale)

as the measure.

Example:

SortingIssue_2.PNGSortingIssue_1.PNG

As you can see, the two 0% dimensions are marked in the first picture where they have a different value for the same measure. That's because in the first table, there is no [Magazinno] added, so the measure is calculated on all the [GruppoMerceologico].
When selecting a [Magazinno], you are limiting the data on which the measure is calculated and it will sort by your measure as you'd expect it to do.

 

 

Perhaps with the cause of the issue revealed, it'll be easier to find a solution for the problem.

 

gloria_bertini
Creator
Creator
Author

Hi,

Thanks for the reply.

I'll see if i can find a solution for this problem.

Kind regards

gloria_bertini
Creator
Creator
Author

Thanks but it didn't work
timpoismans
Specialist
Specialist

Hi Gloria

Please try to replace your second dimension (GruppoMerceologico) with the following:

=Aggr(Dual(GruppoMerceologico,RowNo(total)),Magazzino,GruppoMerceologico)

This gives me the following sorting:

SortingIssue_Solved1.PNG

 

Kind regards,

Tim P.

timpoismans
Specialist
Specialist

Attaching your test qvf with two test-sheets with the solution (hopefully at least).

In .zip format 'cause can't upload .qvf files.

gloria_bertini
Creator
Creator
Author

Thank you very much, unfortunately the user must be free to move the dimension of the pivot between the various levels, in this case the sort no longer works properly.
The solution you proposed is still very interesting.
timpoismans
Specialist
Specialist

Hi Gloria

 

I just messed around with my dimensions in the test app and sorting still seems to sort by highest to lowest percentage even after switching dimensions around.

 

Could you provide an example where the sorting fails after changing dimensions?

gloria_bertini
Creator
Creator
Author

Hi Tim,

sorry for late in reply.

I have a problem using this function 

=Aggr(Dual(GruppoMerceologico,RowNo(total)),Magazzino,GruppoMerceologico) for dimension GruppoMerceologico

when the user change the order of dimensions in the pivot, like in the immage.

In this case there are some error.

Kind regards

 

timpoismans
Specialist
Specialist

Hi Gloria

Yes, that's because of the dual and aggr function. The first "GINEB" technically isn't the same as the second "GINEB" because of the function.

You could remove the "Magazinno" from the function, it will show you the values of magazinno per group, but it won't sort properly. The deeper levels are sorted properly again though.

 

Not entirely sure how to fix this, sorry.

gloria_bertini
Creator
Creator
Author

Hi,

Thanks the same for help.

Perhaps it is necessary to create parametric dimension.

When i can i will try this solution.

Pheraps it's necessary to create parametric dimension.

When i can i will try this solution.

Kind regards