Announcements
cancel
Showing results for
Did you mean:
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?

Kind regards

19 Replies
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:

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.

Creator
Author

Hi,

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

Kind regards

Creator
Author
Thanks but it didn't work
Specialist

Hi Gloria

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

This gives me the following sorting:

Kind regards,

Tim P.

Specialist

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

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

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.
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?

Creator
Author

Hi Tim,

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

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.

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