5 Replies Latest reply: Oct 27, 2011 5:59 PM by Hector Ponce RSS

    Function "total" in scritp

      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.

        • Function "total" in scritp

          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

            • Function "total" in scritp

              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;

                • Function "total" in scritp

                  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

                    • Re: Function "total" in scritp

                      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;

                        • Re: Function "total" in scritp

                          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