Skip to content

Dashboard StreamlineToQliksense

Jump to bottom
DanielaZrLC edited this page Jun 14, 2021 · 10 revisions

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]

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

Clone this wiki locally