Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)))
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)))
Thanks Sunny, I am implementing the expressions with the real data to see if it behaves as it should.
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
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?
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
What would be the value of this in the above example?
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
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)))