Dashboard StreamlineToQliksense - Genomma-Lab-Internacional/businessintelligence GitHub Wiki

Conexiones

Las conexiones para este Dashboard se encuentran en AWS:

Los nombres de las conexiones de QlikSense se llaman:

  • streamlinePTPlanMateriales

  • streamlinePTAvance

Y corresponden a las URLs respectivamente.

Mapping

  • MarcaNecMapping: Estandarización de nombres de marcas NEC Bio Electro, Losec A y Medicasp.
  • MarcaMapping: Estandarización de nombres de marcas SISTEMA GB, XL-3, POMADA DE LA CAMPANA, LOMECAN V, DERMOPRADA, SILKAMEDIC, SILUET 40, MEDICASP, COLONIA SANBORNS.
  • CompradorMapping: Estandarización de nombres de compradores 'Yumiko Martínez', 'Enrique Celada','Bibiana Peralta','Raquel Juárez','Rene/Mario','Raquel/Mario'','Ismael Dávila','Mario'','Israel Rodríguez','Oscar René Salazar','Saul Gomez'.

Schemas

  • Schema para joinItemCodeMiscelanea

    • BU
    • ApplyMap ('MarcaMapping',Marca) AS Marca,
    • Item code,
    • Supplier,
    • "Nal/Exp",
    • Comprador,
    • if("Jun 2021">"Jun 20213",'Volúmen menor al solicitado',
    • if("Jun 2021"<"Jun 20213",'Volúmen mayor al solicitado',
    • if("Jun 2021"="Jun 20213", 'Volúmen igual al solicitado', null()))) as validacion,
    • Description,
    • "On hand",
    • "Days of supply" as "Days of supply",
    • "To ship",
    • "To receive",
    • "Lead time days" as "Lead time, days",
    • "Order cycle months" as "Order cycle, months",
    • "Min lot",
    • "Safety stock periods" as "Safety stock periods",
    • "Safety stock" as "Safety stock",
    • "Marca ABC",
    • "SKU ABC",
    • "Meses en OC"

  • Schema para orderingPlan:

    • Item code,
    • 0 AS [May 2021],
    • [Jun 2021],
    • [Jul 2021],
    • [Aug 2021],
    • [Sep 2021],
    • [Oct 2021],
    • [Nov 2021],
    • [Dec 2021],
    • [Jan 2022],
    • [Feb 2022],
    • [Mar 2022],
    • [Apr 2022],
    • [May 2022],
    • [May 2021] **AS ** Total,
    • 'Ordering Plan' AS [type]
       Esquema de meses desde el mes y año corriente hasta el mes anterior al corriente con proyección a 1 año.

  • Schema para demandForecast y Plan KPI *Sólo cambia el valor [Type]

    • [Jun 20211] as [Jun 2021],
    • [Jul 20211] as [Jul 2021],
    • [Aug 20211] as [Aug 2021],
    • [Sep 20211] as [Sep 2021],
    • [Oct 20211] as [Oct 2021],
    • [Nov 20211] as [Nov 2021],
    • [Dec 20211] as [Dec 2021],
    • [Jan 20221] as [Jan 2022],
    • [Feb 20221] as [Feb 2022],
    • [Mar 20221] as [Mar 2022],
    • [Apr 20221] as [Apr 2022],
    • [May 20221] as [May 2022],
    • 0 as Total,
    • 'Demand Forecast' as [type] ó 'Plan KPI' as [type] (según sea el caso)
       Esquema de meses desde el mes y año corriente hasta el mes anterior al corriente con proyección a 1 año.

  • Schema para projectedInventoryLevels y projectedInventoryLevelsKPI *Sólo cambia el valor [Type]

    • "Item code",
    • "On hand" as [May 2021],
    • [Jun 20212] as [Jun 2021],
    • [Jul 20212] as [Jul 2021],
    • [Aug 20212] as [Aug 2021],
    • [Sep 20212] as [Sep 2021],
    • [Oct 20212] as [Oct 2021],
    • [Nov 20212] as [Nov 2021],
    • [Dec 20212] as [Dec 2021],
    • [Jan 20222] as [Jan 2022],
    • [Feb 20222] as [Feb 2022],
    • [Mar 20222] as [Mar 2022],
    • [Apr 20222] as [Apr 2022],
    • [May 20222] as [May 2022],
    • 0 as Total,
    • 'Projected Inventory Levels' as [type] ó 'Projected Inventory Levels KPI' as [type] (según sea el caso)

  • Schema para SafetyStock

    • Item code],
    • "Safety stock" as [May 2021],
    • "Safety stock" as [Jun 2021],
    • "Safety stock" as [Jul 2021],
    • "Safety stock" as [Aug 2021],
    • "Safety stock" as [Sep 2021],
    • "Safety stock" as [Oct 2021],
    • "Safety stock" as [Nov 2021],
    • "Safety stock" as [Dec 2021],
    • "Safety stock" as [Jan 2022],
    • "Safety stock" as [Feb 2022],
    • "Safety stock" as [Mar 2022],
    • "Safety stock" as [Apr 2022],
    • "Safety stock" as [May 2022],
    • 0 as Total,
    • 'Safety Stock' as [type]

  • Schema para stockout

    • BU as sBU,
    • Marca as sMarca,
    • "Nal/Exp" as [sNal/Exp],
    • Comprador as sComprador,
    • "Item code" as [sItem code],
    • Description as sDescription,
    • Forecast as Jun2021Forecast,
    • Stokout as Jun2021Stockout,
    • Forecast - Stokout as Jun2021Restricta

  • Schema para cumplimiento

    • "Item code" as [consolidado-Item code],
    • Description as [cDescription],
    • BU as [cBU],
    • Marca as [cMarca],
    • "Nal/Exp" as [cNal/Exp],
    • "Comprador Streamline" as [cComprador Streamline],
    • "Mayo Streamline" as [cStreamline],
    • Maquilador as [cMaquilador],
    • "03-may al 09-may" as [c-p-03-may al 09-may],
    • "10-may al 16-may" as [c-p-10-may al 16-may],
    • "17-may al 23-may" as [c-p-17-may al 23-may],
    • "24-may al 30-may" as [c-p-24-may al 30-may],
    • "03-may al 09-may"+"10-may al 16-may" + "17-may al 23-may" + "24-may al 30-may" as [cTotal Programa],
    • "03-may al 09-may1" as [c-e-03-may al 09-may],
    • "10-may al 16-may1" as [c-e-10-may al 16-may],
    • "17-may al 23-may1" as [c-e-17-may al 23-may],
    • "24-may al 30-may1" as [c-e-24-may al 30-may],
    • "03-may al 09-may1"+"10-may al 16-may1" + "17-may al 23-may1" + "24-may al 30-may1" as [cTotal Entradas],
    • if("03-may al 09-may"+"10-may al 16-may"... [cTotal Programa] > "03-may al 09-may1"+"10-may al 16-may1"... cTotal Entradas , asigna cTotal Entradas ) AS cReal vs Programa (Resumido),
    • if("Mayo Streamline"> "03-may al 09-may1" + ... "c-e-10-may al 16-may" "c-e-17-may al 23-may" "c-e-24-may al 30-may","03-may al 09-may1" + ... "c-e-10-may al 16-may" "c-e-17-may al 23-may" "c-e-24-may al 30-may", "Mayo Streamline") as cReal vs Streamline (Resumido),
    • if("03-may al 09-may">"03-may al 09-may1","03-may al 09-may1","03-may al 09-may") as [c-pe-03-may al 09-may],
    • if("10-may al 16-may">"10-may al 16-may1","10-may al 16-may1","10-may al 16-may") as [c-pe-10-may al 16-may],
    • if("17-may al 23-may">"17-may al 23-may1","17-may al 23-may1","17-may al 23-may") as [c-pe-17-may al 23-may],
    • if("24-may al 30-may">"24-may al 30-may1","24-may al 30-may1","24-may al 30-may") as [c-pe-24-may al 30-may]

  • Schema para **materials-joinItemCodeMiscelanea **

    • Item code as materials-Item code,
    • [BU] as [mBU],
    • ApplyMap('MarcaMapping',[Marca]) as [mMarca],
    • Supplier as mSupplier,
    • "Nal/Exp" as "mNal/Exp",
    • Comprador as mComprador,
    • Forecast as Jun2021Forecast,
    • Stokout as Jun2021Stockout,
    • Forecast - Stokout as Jun2021Restricta
    • if([Jun 2021] * 0.85 > [Jun 20213],'Volúmen menor al solicitado',
    • if([Jun 2021] * 1.15 < [Jun 20213],'Volúmen mayor al solicitado',
    • 'Volúmen igual al solicitado')) as mValidacion,
    • "Compra Directa" as "mCompra Directa",
    • "On hand" as "mOn hand",
    • Description as mDescription,
    • "Days of supply" as "mDays of supply",
    • "To ship" as "mTo ship",
    • "To receive" as "mTo receive",
    • "Lead time, days" as "mLead time, days",
    • "SS periods" as "mSS periods",
    • "Min lot" as "mMin lot",
    • "Safety stock" as "mSafety stock",
    • Clasificacion as mClasificacion,
    • ABC as mABC,
    • Meses en OC as mMeses en OC

  • Schema para **materials-orderingPlan, materials-demandForecast, materials-projectedInventoryLevels, materials-planKPI, materials-projectedInventoryLevelsKPI ** *Sólo cambia el valor [Type]

    • Item code as materials-Item code,
    • 0 as mMay 2021,
    • Jun 2021 as [mJun 2021],
    • Jul 2021 as [mJul 2021],
    • Aug 2021 as [mAug 2021],
    • Sep 2021 as [mSep 2021],
    • Oct 2021 as [mOct 2021],
    • Nov 2021 as [mNov 2021],
    • Dec 2021 as [mDec 2021],
    • Jan 2022 as [mJan 2022],
    • Feb 2022 as [mFeb 2022],
    • Mar 2022 as [mMar 2022],
    • Apr 2022 as [mApr 2022],
    • May 2022 as [mMay 2022],
    • May 2021 as mTotal,
    • Ordering Plan as [mtype], 'Demand Forecast' as [mtype], 'Plan KPI' as [mtype] ó 'Projected Inventory Levels' as [mtype], 'Projected Inventory Levels KPI' as [mtype], (según sea el caso)

  • Schema para **materials-safetyStock **

    • [Item code] as [materials-Item code],
    • "Safety stock" as [mMay 2021],
    • "Safety stock" as [mJun 2021],
    • "Safetystock" as [mJul 2021],
    • "Safety stock" as [mAug 2021],
    • "Safety stock" as [mSep 2021],
    • "Safety stock" as [mOct 2021],
    • "Safety stock" as [mNov 2021],
    • "Safety stock" as [mDec 2021],
    • "Safety stock" as [mJan 2022],
    • "Safety stock" as [mFeb 2022],
    • "Safety stock" as [mMar 2022],
    • "Safety stock" as [mApr 2022],
    • "Safety stock" as [mMay 2022],
    • "May 2021" as mTotal,

Script

El script se compone de 9 secciones:

  • Mapping:Estandarización y creación de relaciones entre columnas
  • streamline
  • streamlineCrossTables
  • materials
  • materialsCrossTables
  • stockout
  • cumplimiento
  • cumplimientoCrossTables
  • % Avance

Fórmulas