Function "total" in scritp

[#1]

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.

[#2 · Accepted solution]

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

[#3]

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;

[#4]

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

[#5]

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;

[#6]

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