Problem to sort by measures in a pivot table

[gloria_bertini]

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

[timpoismans]

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.

[gloria_bertini]

Hi,

Thanks for the reply.

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

Kind regards

[gloria_bertini]

Thanks but it didn't work

[timpoismans]

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:

Kind regards,

Tim P.

[timpoismans]

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]

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]

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]

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]

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]

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