18 Replies Latest reply: Oct 20, 2015 6:05 PM by hector espinosa RSS

    Inventory Turnover

      Hi;

       

      I just can't find out how to look at my turnover rate por the past.

       

      Formula is pretty simple:

       

      (Sum of las 12 monts COGS) / Avg Inventory

       

      In excel it's really easy. (See attached table) Example:

       

      Turnover for August is : (Sum of COGS sept 2014 - August 2015) / Avg Inventory (sept 2014 - August 2015).

       

      I Need that formula to apply for every month in the past so I can see what's the turnover rate for every month, so it has to go back and look for past 12 months based on target month.

       

      HELP!!!!!

        • Re: Inventory Turnover
          Satish Kurra

          Create Variables for the months you would like to see on the chart and then use set analysis to achieve the same

           

          Attaching SET Analysis YTD, MTD, WTD and QTD logic for reference.

           

          Thanks

          Satish

          • Re: Inventory Turnover
            Digvijay Singh

            See the attached -

             

            Input:

            LOAD Date(Mes,'MMM-YY') as Mes,

                num(Inventario, '#,##0.00') as Inventario,

                num(F6,'#,##0.00') as Valor,

                num([Coste Cont Venta],'#,##0.00') as [Coste Cont Venta]

            FROM

            Libro2.xlsx

            (ooxml, embedded labels, header is 1 lines, table is Turnover);

             

            Output:

            Load *, num([Coste Cont 12 M]/[Prom Invent],'#,##0.00') as Turnover;

            Load Mes,

              Inventario,

                Valor,

                [Coste Cont Venta],

              if(recno()>=12,num(rangesum([Coste Cont Venta],Peek([Coste Cont Venta],-1),Peek([Coste Cont Venta],-2),Peek([Coste Cont Venta],-3),

                  Peek([Coste Cont Venta],-4),Peek([Coste Cont Venta],-5),Peek([Coste Cont Venta],-6),

                  Peek([Coste Cont Venta],-7),Peek([Coste Cont Venta],-8),Peek([Coste Cont Venta],-9),

                  Peek([Coste Cont Venta],-10),Peek([Coste Cont Venta],-11)),'#,##0.00')) as [Coste Cont 12 M],

             

             

                if(recno()>=12,num(rangeavg(Valor,

                  Peek(Valor,-1),Peek(Valor,-2),Peek(Valor,-3),

                  Peek(Valor,-4),Peek(Valor,-5),Peek(Valor,-6),

                  Peek(Valor,-7),Peek(Valor,-8),Peek(Valor,-9),

                  Peek(Valor,-10),Peek(Valor,-11)),'#,##0.00')) as [Prom Invent]

              

                Resident Input;

             

              Drop table Input;

              • Re: Inventory Turnover

                Great, I tried It in as an independent File and It worked perfectly... really appreciate it. The thing is that I've tried to run it in a File where the field mes, inventario valor and cose cont venta , where previously loaded, how do I have to run the script, can't find a way...  digvijay

                  • Re: Inventory Turnover
                    Digvijay Singh

                    You mean you need to integrate it with existing script having basic fields already loaded. If you can share that script, we can put the required stuff at right place.

                      • Re: Inventory Turnover

                        Sure:

                         

                        Script goes and LOADS data from 2 different tables, they load at the same time as lots more:

                         

                        Ruta_BD ='\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\';

                         

                         

                        [Base_de_Datos_ND]:

                        LOAD *

                        FROM

                        $(Ruta_BD)*.txt

                          (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                         

                         

                         

                        SET ThousandSep='.';

                        SET DecimalSep=',';

                        SET MoneyThousandSep='.';

                        SET MoneyDecimalSep=',';

                        SET MoneyFormat='$ #.##0,;-$ #.##0,';

                        SET TimeFormat='h:mm:ss';

                        SET DateFormat='DD-MM-YYYY';

                        SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

                        SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

                        SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';

                         

                         

                        SET [V_Mes_Tablero_Control]='Mes 0';

                         

                         

                         

                         

                        // Archivos de Texto

                         

                         

                        Set Ruta_BD ='\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\';

                         

                         

                        [Base_de_Datos_ND]:

                        LOAD *

                        FROM

                        $(Ruta_BD)*.txt

                          (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                         

                         

                         

                         

                        //Variables Stock

                         

                         

                        SET [V_Desc_Filtro_Stock]='';

                        SET [V_Color_Filtro_Stock]='RGB(151,216,1)';

                        LET [V_m3_Stock_Disponible]='sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'}>}m3_Stock_Actual)';

                        LET [V_m3_Stock_No_Deseado]='sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]={'&CHR(39)&'Calidad'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Inmovilizado]={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Línea]={'&CHR(39)&'NO Línea'&CHR(39)&'},[Status_Inmovilizado]-={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Línea]={'&CHR(39)&'Línea Sin Stock'&CHR(39)&'},[Status_Inmovilizado]-={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)';

                         

                         

                        //Carga Semanas de ETA Destino

                        Semanas_ETA:

                         

                         

                        Load [Fecha_ETA_Destino],

                        FirstValue(Semana_ETA_Destino)as Semana_ETA_Destino

                        Group by [Fecha_ETA_Destino];

                         

                         

                        LOAD

                             Fecha_ETA_Destino,

                             If([Fecha_ETA_Destino]<Today(),'','Sem '&Text(Week(Fecha_ETA_Destino)-week(today())))as Semana_ETA_Destino

                        FROM

                        [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\QV-AU01- Tránsitos Base Planeación -.txt]

                        (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                         

                         

                         

                         

                        Status_CDC:

                        LOAD [Entrega Vta] as Entrega,

                             Status_CDC

                            

                        FROM

                        [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\CDC.xlsx]

                        (ooxml, embedded labels, table is Hoja1);

                         

                         

                        Status_Copa_America:

                         

                         

                        LOAD Solicitante,

                             Status_Copa_America

                        FROM

                        [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\Clientes_Copa_America.xlsx]

                        (ooxml, embedded labels, table is Hoja1);

                         

                         

                         

                         

                        EXIT Script

                          • Re: Inventory Turnover
                            Digvijay Singh

                            Are the fields in the sample like Mes,Inventario,Valor, Coste Cont Venta exists in [Base_de_Datos_ND]. I think they are loaded from text file with load * so didn't appear directly in your script.


                            If my above understanding is true then the right place for new script is after below script code -

                            Set Ruta_BD ='\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\';

                             

                            [Base_de_Datos_ND]:

                            LOAD *

                            FROM

                            $(Ruta_BD)*.txt

                              (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);


                            Try and amend as needed the below section after above code snippet, need to somehow make the changes in the same table [Base_de_Datos_ND]:

                             

                             

                            Load *, num([Coste Cont 12 M]/[Prom Invent],'#,##0.00') as Turnover;

                            Load Mes,

                              Inventario,

                                Valor,

                                [Coste Cont Venta],

                              if(recno()>=12,num(rangesum([Coste Cont Venta],Peek([Coste Cont Venta],-1),Peek([Coste Cont Venta],-2),Peek([Coste Cont Venta],-3),

                                  Peek([Coste Cont Venta],-4),Peek([Coste Cont Venta],-5),Peek([Coste Cont Venta],-6),

                                  Peek([Coste Cont Venta],-7),Peek([Coste Cont Venta],-8),Peek([Coste Cont Venta],-9),

                                  Peek([Coste Cont Venta],-10),Peek([Coste Cont Venta],-11)),'#,##0.00')) as [Coste Cont 12 M],

                             

                             

                                if(recno()>=12,num(rangeavg(Valor,

                                  Peek(Valor,-1),Peek(Valor,-2),Peek(Valor,-3),

                                  Peek(Valor,-4),Peek(Valor,-5),Peek(Valor,-6),

                                  Peek(Valor,-7),Peek(Valor,-8),Peek(Valor,-9),

                                  Peek(Valor,-10),Peek(Valor,-11)),'#,##0.00')) as [Prom Invent]

                              • Re: Inventory Turnover

                                Doing something wrong: (changed fields with actual fields)

                                 

                                [Base_de_Datos_ND]:

                                LOAD *

                                FROM

                                $(Ruta_BD)*.txt

                                  (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                                 

                                 

                                 

                                Load *, num([Coste_Cont_12_M]/[Prom_Invent],'#.##0,00') as Turnover;

                                Load Mes_FF,

                                  USD_Costo_Venta_Total,

                                     USD_Stock_Cierre,

                                  if(recno()>=12,num(rangesum(USD_Costo_Venta_Total,Peek(USD_Costo_Venta_Total,-1),Peek(USD_Costo_Venta_Total,-2),Peek(USD_Costo_Venta_Total,-3),

                                      Peek(USD_Costo_Venta_Total,-4),Peek(USD_Costo_Venta_Total,-5),Peek(USD_Costo_Venta_Total,-6),

                                      Peek(USD_Costo_Venta_Total,-7),Peek(USD_Costo_Venta_Total,-8),Peek(USD_Costo_Venta_Total,-9),

                                      Peek(USD_Costo_Venta_Total,-10),Peek(USD_Costo_Venta_Total,-11)),'#.##0,00')) as [Coste_Cont_12_M],

                                 

                                 

                                 

                                    if(recno()>=12,num(rangeavg(USD_Stock_Cierre,

                                      Peek(USD_Stock_Cierre,-1),Peek(USD_Stock_Cierre,-2),Peek(USD_Stock_Cierre,-3),

                                      Peek(USD_Stock_Cierre,-4),Peek(USD_Stock_Cierre,-5),Peek(USD_Stock_Cierre,-6),

                                      Peek(USD_Stock_Cierre,-7),Peek(USD_Stock_Cierre,-8),Peek(USD_Stock_Cierre,-9),

                                      Peek(USD_Stock_Cierre,-10),Peek(USD_Stock_Cierre,-11)),'#,##0.00')) as [Prom_Invent];

                                 

                                 

                                 

                                 

                                //Inlines

                                  • Re: Inventory Turnover
                                    Digvijay Singh

                                    Write Resident [Base_de_Datos_ND1]; at end of new script. Like -

                                    [Prom_Invent]

                                    Resident [Base_de_Datos_ND1];


                                    Add  table name [Base_de_Datos_ND]: at the top of new script.

                                    And change [Base_de_Datos_ND]: to [Base_de_Datos_ND1] two places in the beginnning while loading from text file.

                                    We can drop table [Base_de_Datos_ND1] but ensure all fields are available in [Base_de_Datos_ND]. Keeping same name as before while loading new script assuming that it might be referred elsewhere.

                                     

                                     


                                     

                                    Ruta_BD ='\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\';

                                     

                                     

                                    [Base_de_Datos_ND1]:

                                    LOAD *

                                    FROM

                                    $(Ruta_BD)*.txt

                                      (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                                     

                                     

                                     

                                    SET ThousandSep='.';

                                    SET DecimalSep=',';

                                    SET MoneyThousandSep='.';

                                    SET MoneyDecimalSep=',';

                                    SET MoneyFormat='$ #.##0,;-$ #.##0,';

                                    SET TimeFormat='h:mm:ss';

                                    SET DateFormat='DD-MM-YYYY';

                                    SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

                                    SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

                                    SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';

                                     

                                     

                                    SET [V_Mes_Tablero_Control]='Mes 0';

                                     

                                     

                                     

                                     

                                    // Archivos de Texto

                                     

                                     

                                    Set Ruta_BD ='\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\';

                                     

                                     

                                    [Base_de_Datos_ND1]:

                                    LOAD *

                                    FROM

                                    $(Ruta_BD)*.txt

                                      (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                                     

                                    [Base_de_Datos_ND]:


                                    Load *, num([Coste_Cont_12_M]/[Prom_Invent],'#.##0,00') as Turnover;

                                    Load Mes_FF,

                                      USD_Costo_Venta_Total,

                                         USD_Stock_Cierre,

                                      if(recno()>=12,num(rangesum(USD_Costo_Venta_Total,Peek(USD_Costo_Venta_Total,-1),Peek(USD_Costo_Venta_Total,-2),Peek(USD_Costo_Venta_Total,-3),

                                          Peek(USD_Costo_Venta_Total,-4),Peek(USD_Costo_Venta_Total,-5),Peek(USD_Costo_Venta_Total,-6),

                                          Peek(USD_Costo_Venta_Total,-7),Peek(USD_Costo_Venta_Total,-8),Peek(USD_Costo_Venta_Total,-9),

                                          Peek(USD_Costo_Venta_Total,-10),Peek(USD_Costo_Venta_Total,-11)),'#.##0,00')) as [Coste_Cont_12_M],

                                     

                                     

                                     

                                        if(recno()>=12,num(rangeavg(USD_Stock_Cierre,

                                          Peek(USD_Stock_Cierre,-1),Peek(USD_Stock_Cierre,-2),Peek(USD_Stock_Cierre,-3),

                                          Peek(USD_Stock_Cierre,-4),Peek(USD_Stock_Cierre,-5),Peek(USD_Stock_Cierre,-6),

                                          Peek(USD_Stock_Cierre,-7),Peek(USD_Stock_Cierre,-8),Peek(USD_Stock_Cierre,-9),

                                          Peek(USD_Stock_Cierre,-10),Peek(USD_Stock_Cierre,-11)),'#,##0.00')) as [Prom_Invent]

                                    Resident [Base_de_Datos_ND1];

                                     

                                    Drop table [Base_de_Datos_ND1];

                                     

                                     

                                    //Variables Stock

                                     

                                     

                                    SET [V_Desc_Filtro_Stock]='';

                                    SET [V_Color_Filtro_Stock]='RGB(151,216,1)';

                                    LET [V_m3_Stock_Disponible]='sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'}>}m3_Stock_Actual)';

                                    LET [V_m3_Stock_No_Deseado]='sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]={'&CHR(39)&'Calidad'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Inmovilizado]={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Línea]={'&CHR(39)&'NO Línea'&CHR(39)&'},[Status_Inmovilizado]-={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)+sum({$<[Status_Stock]-={'&CHR(39)&'Tránsito'&CHR(39)&'},[Centro-Desc_2]-={'&CHR(39)&'Puertos'&CHR(39)&'},[Status_Stock]-={'&CHR(39)&'Calidad'&CHR(39)&'},[Status_Línea]={'&CHR(39)&'Línea Sin Stock'&CHR(39)&'},[Status_Inmovilizado]-={'&CHR(39)&'Inmovilizado'&CHR(39)&'}>}m3_Stock_Actual)';

                                     

                                     

                                    //Carga Semanas de ETA Destino

                                    Semanas_ETA:

                                     

                                     

                                    Load [Fecha_ETA_Destino],

                                    FirstValue(Semana_ETA_Destino)as Semana_ETA_Destino

                                    Group by [Fecha_ETA_Destino];

                                     

                                     

                                    LOAD

                                         Fecha_ETA_Destino,

                                         If([Fecha_ETA_Destino]<Today(),'','Sem '&Text(Week(Fecha_ETA_Destino)-week(today())))as Semana_ETA_Destino

                                    FROM

                                    [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\QV-AU01- Tránsitos Base Planeación -.txt]

                                    (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

                                     

                                     

                                     

                                     

                                    Status_CDC:

                                    LOAD [Entrega Vta] as Entrega,

                                         Status_CDC

                                       

                                    FROM

                                    [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\CDC.xlsx]

                                    (ooxml, embedded labels, table is Hoja1);

                                     

                                     

                                    Status_Copa_America:

                                     

                                     

                                    LOAD Solicitante,

                                         Status_Copa_America

                                    FROM

                                    [\\10.85.1.7\GDL-Sist-gestion$\21-Inputs_Qlikview\Clientes_Copa_America.xlsx]

                                    (ooxml, embedded labels, table is Hoja1);

                                     

                                     

                                     

                                     

                                    EXIT Script