Help AGGR() - FirstSortedValue()

[PEyzaguirre]

Hola tengo un problema que no puedo resolver y quería solicitar su ayuda. Tengo dos tablas llamadas Producto y Ventas, adjunto imagen:

(Hi, I have a problem that I can not solve and I wanted to request your help. I have two tables called Product and Sales, attached image:)

Debo generar una vista en una Tabla Pivotante o Tabla Simple que debe cumplir los sgtes. requerimientos:

1° Columna debe considerar todos los 'Grupos'.

2° Columna debe considerar solamente un registro del campo 'Nivel_1' por campo Grupo, que corresponde al 'Nivel_1' que tiene mayor venta.

3° Columna debe considerar solamente un registro del campo 'Nivel_2', que corresponde al 'Nivel_2' que tiene mayor venta y que corresponda al 'Nivel_1' seleccionado en la columna 2.

4° Columna debe considerar solamente un registro del campo 'Nivel_3', que corresponde al 'Nivel_3' que tiene mayor venta y que corresponda al 'Nivel_1' seleccionado en la columna 2 y al 'Nivel_2' seleccionado en la columna 3.

5° Columna debe considerar MontoVenta/Cantidad para los registros mostrados con anterioridad.

(

I must generate a view in a Pivoting Table or Simple Table that must meet the following requirements:
1st Column should consider all the 'Groups'.
2nd Column should consider only a record of the 'Level_1' field per Group field, which corresponds to the 'Level_1' that has the highest sales.
3 ° Column should consider only one record of the field 'Level_2', which corresponds to 'Level_2' that has the highest sales and that corresponds to 'Level_1' selected in column 2.
4th Column should consider only a record of the field 'Level_3', which corresponds to 'Level_3' that has the highest sales and that corresponds to 'Level_1' selected in column 2 and to 'Level_2' selected in column 3.
5th Column must consider MontoVenta / Amount for the records shown previously.

)

Dejo un ejemplo de los datos:

(I leave an example of the data:)

El resultado esperado:

(The expected result:)

Usé la sgte. expresión pero solamente me resulta en 'Nivel_1', por otro lado no se donde incluir MontoVenta/Cantidad:

(I used this expression but it only results in 'Level_1', on the other hand I do not know where to include MontoVenta/Cantidad)

FirstSortedValue(Nivel_1, -Aggr(Sum ({<Año = {"$(MaxAño)"}>}MontoVenta), Nivel_1, Grupo))

Muchas gracias de antemano disculpen la traducción.

(Many thanks in advance, sorry for the translation, Google Translate)

[sunny_talwar · Accepted solution]

Try these expressions

FirstSortedValue(Nivel_1, -Aggr(Sum(MontoVenta), Nivel_1, Grupo))

FirstSortedValue(Nivel_2, -(Aggr(NODISTINCT Sum(MontoVenta), Nivel_1, Grupo) + (Aggr(Sum(MontoVenta), Nivel_2, Nivel_1, Grupo)/1E4)))

FirstSortedValue(Nivel_3, -(Aggr(NODISTINCT Sum(MontoVenta), Nivel_1, Grupo) + (Aggr(NODISTINCT Sum(MontoVenta), Nivel_2, Nivel_1, Grupo)/1E4)+(Aggr(NODISTINCT Sum(MontoVenta), Nivel_3, Nivel_2, Nivel_1, Grupo)/1E8)))

FirstSortedValue(Aggr(Sum(MontoVenta), Nivel_1, Nivel_2, Nivel_3), -(Aggr(NODISTINCT Sum(MontoVenta), Nivel_1, Grupo) + (Aggr(NODISTINCT Sum(MontoVenta), Nivel_2, Nivel_1, Grupo)/1E4)+(Aggr(NODISTINCT Sum(MontoVenta), Nivel_3, Nivel_2, Nivel_1, Grupo)/1E8)))

[PEyzaguirre]

Thanks Sunny, I am implementing the expressions with the real data to see if it behaves as it should.

[PEyzaguirre]

sunny, the expressions worked perfect. Although I still have a couple of doubts:
- What does the '/ 1E8' and '/ 1E4' mean if you could explain to me?
- In what part of the last expression should I consider the formula 'MontoVenta / Cantidad'?

Gracias

[sunny_talwar]

IE4 = 10000 and IE8 = 100000000 and I am using this to give different sort weights.

I don't understand your second question? Can you elaborate?

[PEyzaguirre]

Sorry sunny, I forgot to consider the 'Cantidad' field in the data table.

The 4th column must represent the division of 'MontoVenta' by 'Cantidad' (MontoVenta / Cantidad),
this for the values ​​selected in the columns Group, Level_1, Level_2, Level_3.

Thank's

[sunny_talwar]

What would be the value of this in the above example?

[PEyzaguirre]

Hello Sunny, the values ​​should be 10 and 2.7 respectively, But do not worry since I solved it by modifying in the expression Sum(MontoVenta) by Sum(MontoVenta)/Sum(Cantidad)

I thank you for the help provided.

Regards

[sunny_talwar]

You can also try this

FirstSortedValue(Aggr(Sum(MontoVenta/Cantidad), Nivel_1, Nivel_2, Nivel_3), -(Aggr(NODISTINCT Sum(MontoVenta), Nivel_1, Grupo) + (Aggr(NODISTINCT Sum(MontoVenta), Nivel_2, Nivel_1, Grupo)/1E4)+(Aggr(NODISTINCT Sum(MontoVenta), Nivel_3, Nivel_2, Nivel_1, Grupo)/1E8)))