Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to know if there any function that can be used in the script as the one used in the expressions of "total".
I need to split the sale between the total sales in the script.
example
sum (sales / total sales).
Greetings.
Lets say your table name is SalesTable:
Temp:
Load sum(Sales) as totSales Resident SalesTable;
Let vTotalSales = peek('totSales',0,'Temp');
Drop table Temp;
Join(SalesTable)
Load *,Sales/$(vTotalSales) as SaleShare Resident SalesTable;
Hope this helps,
Kiran
Lets say your table name is SalesTable:
Temp:
Load sum(Sales) as totSales Resident SalesTable;
Let vTotalSales = peek('totSales',0,'Temp');
Drop table Temp;
Join(SalesTable)
Load *,Sales/$(vTotalSales) as SaleShare Resident SalesTable;
Hope this helps,
Kiran
This is my script please help me.
VentaOriginal:
CrossTable(AGRUPADOR, DATA, 14)
Buffer (Incremental) LOAD YEAR(DATE) AS AÑO,
MONTH(DATE) AS MES,
PLANT,
MATERIAL AS [NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
[SALES MXN] AS TOTAL,
UTOPE /[SALES MXN] as ABC1,
[DIST CHANNEL],
SECTOR,
[SALES MXN] AS [01_VENTAS],
MATERIAL1 + [VAR MAT] AS [02_MATERIA PRIMA],
[MARGEN BRUTO] AS [03_MARGEN COMERCIAL],
[GTOS UP] AS [04_GTOS DIR. UNIDAD. PROD.],
[VAR GTOS] AS [05_VARIAC. DE PRODUCC.],
[CTO CONV STD] AS [06_CTO. CONVERSION],
UTILIDAD AS [07_UTILIDAD BRUTA],
[CTO DIST] AS [08_COSTO DE DISTRIB],
[CONT. MARG.] AS [09_CONTRIB. MARG. PDTO.],
[GTOS US] AS [10_GTOS PROD UNID. SERV.],
[EBITDA PLANTA] AS [11_EBITDA PLANTA],
DEPREC AS [12_DEPREC. Y AMORT.],
[UTILIDAD PLANTA] AS [13_UTILIDAD PLANTA],
[GTOS ADMON] AS [14_GTOS ADMON Y VTA],
UTOPE AS [15_UTOPE]
FROM
C:\Users\Raul\Desktop\QV\PDR\Vtas_09.xlsx
(ooxml, embedded labels, table is SALIDA);
VENTA:
LOAD
AÑO,
MES,
makedate(AÑO, MES) AS keyTC,
PLANT,
[NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
TOTAL,
if(TOTAL/TOTAL >=.95,'C',
if(TOTAL/TOTAL>=0.80,'B',
if(TOTAL/TOTAL >=.50,'A',
if(TOTAL/TOTAL<=0.50,'AA')))) as ABC_VENTA,
ABC1,
if(ABC1 >=.10,'AA',
if(ABC1 >=.07, 'A',
if(ABC1 >=.0, 'B',
if(ABC1<.0,'C')))) as ABC_UTOPE,
[DIST CHANNEL],
AGRUPADOR,
AGRUPADOR as CONCEPTO,
Right(AGRUPADOR,(len(AGRUPADOR)-3)) as Nivel1,
DATA,
DATA as DATA_AVER
Resident VentaOriginal;
DROP Table VentaOriginal;
Modified the code for the total you need as well as some optimization.
VentaOriginal:
CrossTable(AGRUPADOR, DATA, 14)
Buffer (Incremental) LOAD YEAR(DATE) AS AÑO,
MONTH(DATE) AS MES,
PLANT,
MATERIAL AS [NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
[SALES MXN] AS TOTAL,
UTOPE /[SALES MXN] as ABC1,
[DIST CHANNEL],
SECTOR,
[SALES MXN] AS [01_VENTAS],
MATERIAL1 + [VAR MAT] AS [02_MATERIA PRIMA],
[MARGEN BRUTO] AS [03_MARGEN COMERCIAL],
[GTOS UP] AS [04_GTOS DIR. UNIDAD. PROD.],
[VAR GTOS] AS [05_VARIAC. DE PRODUCC.],
[CTO CONV STD] AS [06_CTO. CONVERSION],
UTILIDAD AS [07_UTILIDAD BRUTA],
[CTO DIST] AS [08_COSTO DE DISTRIB],
[CONT. MARG.] AS [09_CONTRIB. MARG. PDTO.],
[GTOS US] AS [10_GTOS PROD UNID. SERV.],
[EBITDA PLANTA] AS [11_EBITDA PLANTA],
DEPREC AS [12_DEPREC. Y AMORT.],
[UTILIDAD PLANTA] AS [13_UTILIDAD PLANTA],
[GTOS ADMON] AS [14_GTOS ADMON Y VTA],
UTOPE AS [15_UTOPE]
FROM
C:\Users\Raul\Desktop\QV\PDR\Vtas_09.xlsx
(ooxml, embedded labels, table is SALIDA);
Temp:
Load sum(TOTAL) as totSales Resident VentaOriginal;
Let vTotSales = peek('totSales',0','Temp');
VENTA:
LOAD
AÑO,
MES,
makedate(AÑO, MES) AS keyTC,
PLANT,
[NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
TOTAL,
if(TOTAL/vTotSales >=.95,'C',
if(TOTAL/vTotSales>=0.80,'B',
if(TOTAL/vTotSales >=.50,'A','AA'))) as ABC_VENTA,
ABC1,
if(ABC1 >=.10,'AA',
if(ABC1 >=.07, 'A',
if(ABC1 >=.0, 'B','C'))) as ABC_UTOPE,
[DIST CHANNEL],
AGRUPADOR,
AGRUPADOR as CONCEPTO,
Right(AGRUPADOR,(len(AGRUPADOR)-3)) as Nivel1,
DATA,
DATA as DATA_AVER
Resident VentaOriginal;
DROP Tables VentaOriginal,Temp;
Regards,
Kiran
Hello
there was a change of plans, if I served you told me but it did not the expected result as it gives a different result.
I need to do the same but now the total of DATA but I do just the AGRUPADOR= '01_VENTAS 'can do this?
This is the script cque data must make the total
VentaOriginal:
CrossTable(AGRUPADOR, DATA, 14)/*era 20*/
Buffer (Incremental) LOAD YEAR(DATE) AS AÑO,
MONTH(DATE) AS MES,
PLANT,
MATERIAL AS [NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
UTOPE /[SALES MXN] as ABC1,
[DIST CHANNEL],
SECTOR,
[SALES MXN] AS [01_VENTAS],
MATERIAL1 + [VAR MAT] AS [02_MATERIA PRIMA],
[MARGEN BRUTO] AS [03_MARGEN COMERCIAL],
[GTOS UP] AS [04_GTOS DIR. UNIDAD. PROD.],
[VAR GTOS] AS [05_VARIAC. DE PRODUCC.],
[CTO CONV STD] AS [06_CTO. CONVERSION],
UTILIDAD AS [07_UTILIDAD BRUTA],
[CTO DIST] AS [08_COSTO DE DISTRIB],
[CONT. MARG.] AS [09_CONTRIB. MARG. PDTO.],
[GTOS US] AS [10_GTOS PROD UNID. SERV.],
[EBITDA PLANTA] AS [11_EBITDA PLANTA],
DEPREC AS [12_DEPREC. Y AMORT.],
[UTILIDAD PLANTA] AS [13_UTILIDAD PLANTA],
[GTOS ADMON] AS [14_GTOS ADMON Y VTA],
UTOPE AS [15_UTOPE]
FROM
C:\Users\Raul\Desktop\QV\PDR\Vtas_09.xlsx
(ooxml, embedded labels, table is SALIDA);
Temp:
Load sum(DATA) as totSales Resident VentaOriginal;
Let vTotSales = peek('totSales',0,'Temp');
VENTA:
LOAD
AÑO,
MES,
makedate(AÑO, MES) AS keyTC,
PLANT,
[NO_PARTE],
MODEL,
MARKET,
PRODUCT,
[CUSTOMER ORIG],
CUSTOMER,
COUNTRY,
TYPE,
if(DATA/$(vTotSales) >=.95,'C',
if(DATA/$(vTotSales)>=0.80,'B',
if(DATA/$(vTotSales) >=.50,'A',
if(DATA/$(vTotSales)<=0.50,'AA')))) as ABC_VENTA,
ABC1,
if(ABC1 >=.10,'AA',
if(ABC1 >=.07, 'A',
if(ABC1 >=.0, 'B',
if(ABC1<.0,'C')))) as ABC_UTOPE,
[DIST CHANNEL],
SECTOR,
AGRUPADOR,
AGRUPADOR as CONCEPTO,
Right(AGRUPADOR,(len(AGRUPADOR)-3)) as Nivel1,
DATA,
DATA as DATA_AVER
Resident VentaOriginal;
DROP Table VentaOriginal, Temp;
thanks but I found the answer, was just using a WHERE .
Temp:
Load sum(DATA) as totSales
Resident VentaOriginal
Where AGRUPADOR='01_VENTAS';
Let vTotSales = peek('totSales',0,'Temp'); This variable is good.
Let vSales = Rangesum(totSales); This variable is bad just send 0
But now I need another variable that is only the sum (DATA)
What I did was a pure RANGESUM but sends me 0 me with this variable could support