topic Re: Inventory Turnover in QlikView

Inventory Turnover

Mon, 26 Jan 2026 18:19:17 GMT

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

satishkurra — Sat, 19 Sep 2015 18:14:32 GMT

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 — Sat, 19 Sep 2015 19:12:06 GMT

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

Tue, 22 Sep 2015 16:32:24 GMT

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 — Tue, 22 Sep 2015 18:01:14 GMT

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

Tue, 22 Sep 2015 18:05:45 GMT

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 — Tue, 22 Sep 2015 18:30:20 GMT

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

Tue, 22 Sep 2015 18:49:32 GMT

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 — Tue, 22 Sep 2015 19:18:27 GMT

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)';

//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

Tue, 22 Sep 2015 22:28:09 GMT

It gives me an error:

Field not found - <Mes_FF>

Load

[Mes_FF] as 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]

Re: Inventory Turnover

Tue, 22 Sep 2015 23:07:12 GMT

Actually there's something I didn't consider, data is all scrambled in the tables... there a COGS for a given material for a given month, and then another for a different material and so on.. the same for the Inventory.

Re: Inventory Turnover

Digvijay_Singh — Wed, 23 Sep 2015 03:14:52 GMT

Based on your actual script shared above if you can share actual data and the required new columns with required formula, it won't take much time to fit in our script.

The data from text files are loaded with load * so I cannot see which are available there and elsewhere, if you can share sample text files with real format, and required output then I can help.

Re: Inventory Turnover

Mon, 05 Oct 2015 22:12:17 GMT

Hi, been trying to download both files I use, but one of them is really big and haven't been able to downloadit in excel format so I can change data. (Company policies) . I have one of the files ready so you can have an Idea.

Re: Inventory Turnover

Mon, 05 Oct 2015 22:18:36 GMT

How can I send the .exe file

Re: Inventory Turnover

Mon, 12 Oct 2015 20:30:40 GMT

Hi, did you have time to check it out??

Re: Inventory Turnover

Digvijay_Singh — Tue, 13 Oct 2015 03:35:05 GMT

Hi,

You can attach here sample data, there is a 'Use advanced editor' button on top right to do the same. Use 'Attach' in next page.

you may try to manually copy data in excel or can send snapshot image of data. I need to use it so that I can run your script along with the newly added section to meet your requirement. If we don't understand our source data it is difficult to understand the real problem we are encountering while making updates.

Thanks,

Digvijay

Re: Inventory Turnover

Mon, 19 Oct 2015 22:46:41 GMT

did you get my message ??

Re: Inventory Turnover

Digvijay_Singh — Tue, 20 Oct 2015 03:29:20 GMT

Which one! I wrote on 13th and waiting for your response.

Re: Inventory Turnover

Tue, 20 Oct 2015 22:05:55 GMT

uploaded a document and mentioned you