General presentation

The project

This dashboard is the result of the "RES Key Account Dashboard" IS project.

Code : 3297

Initial description of the project request :

"With the changing dynamics of the markets, pricing alone will not suffice. Key differentiation is enhanced customer centric service levels. KA management with customers must take decisions based on facts and ability to communicate with customers without prohibitive efforts. Views need to be global and to be able to see the real issue quickly, to sustain agreed service levels between the business and customers. Today  this is not possible.

The necessary KPIs are compiled manually on a monthly basis and send widely (BU, SC, Management) but the manual consolidation and formatting / data crunching is too heavy, slow, recurrent and prone to human error.

The project aims at improving the productivity by automatizing that workload and allowing business to access this information quickly, directly formatted and up to date.

The dashboard exists ultimately to support and enhance business in its relationships with customers, responding in timely fashion and to even challenge customers’ wrong perceptions."

 Project team

Access for the dashboard

The access on this dashboard is "all or nothing". There is no way to restrict the access inside the dashboard.

Access is to be approved by the dashboard owner.

Linked dashboards

The main dashboard (Production) is RES Key Account Dashboard

The development version of the dashboard is RES Key Account Dashboard DEV

Source location & documentation

You can click on the "Name Source" Links to get to the detailed documentation page.

Join between sources

• For Stock data, Ship to KA is defined just with the material code. We join stock value with all KA wich are connected with the material and have Qty Sold during Y and Y-1.
• For Forecast Accuracy Data, for the join with Material Code, we just recover the dimension Product Name

Common Attributes and naming

Mapping

Mainly mapping in application:

Mapping Statut Claims

All mapping are in this URL : \\FRPARQLAP01\f$\Qlikview Storage\Private Data\Source Documents\In Production\RES KA\Sources\MANUAL_SOURCES

Mapping Contractual Leadtime

We have a contractual Leadtime value for combination of each ship to key and material key.

Moreover, we give the month value for each loading and we keep the historical data for Y and Y-1:

[$(VS_QVW_NAME)]:

LOAD Evaluate([SHIP_TO_KEY])*1&'-'&Evaluate([MATERIAL_KEY])*1 AS KA_JOIN_KEY,
 
[Lead time days],
 
monthName(makedate(year(today()),num(month(today())),1)) AS MONTH_YEAR,
 
FROM
 
[$(VS_FLD_SOURCE_MANUAL)Shared Files\Contractual lead time.xlsx]
(ooxml, embedded labels, table is Sheet1);

IF FileSize('$(VS_FLD_STORE_SOURCE_QVD)$(VS_QVW_NAME).QVD') > 0 then
 
Concatenate ([$(VS_QVW_NAME)])
Load *
 From [$(VS_FLD_STORE_SOURCE_QVD)$(VS_QVW_NAME).QVD] (qvd)
 where monthName(makedate(year(today()),num(month(today())),1)) <> MONTH_YEAR
 and Year(MONTH_YEAR) >=YEAR(TODAY())-1;

Specific Data

Others new attributes in dashboard:

• RANK_SALES: We calculate the ranking of SHIP TO KA by SALES (€) (Descending Ranking). For this ranking, we keep Sales of Y and Y-1 and we remove the following KA :
  • ‘DIVERS’
  • ‘DIVERS APRES DECISION’
  • ‘SOLVAY’
• SALES_3M_QTY:   This attribute is calculated at the least aggregate level.
  • We reallocated each value of Sales at M+1 and M+2 :
    • FOR i=1 to 2
       JOIN (TABLE)
       LOAD 
      _TEMP_KEY, //KEY

AddMonths(MONTH_YEAR, $) AS MONTH_YEAR, // Key

[Quantity -PUQ-] AS Quantity_$
 Resident TABLE;
next;

• 
  
  • We calculated the sum of Sales like this :
    • SALES_3M_QTY = [Quantity -PUQ-]+ Quantity_1 + Quantity_2
  • We remove temporary fields :    Quantity_1 and Quantity_2
• STOCK_COEFF: We calculate a coefficient for each combination of KA and Material. The Sum of this coefficient for each Material is equal to 1. We calculate this coefficient for affected correctly stock value of material to different KA. This coefficient is determined by the quantity sales by KA for each Material during Y and Y-1. If we have stock value for one material but

Example:

STOCK

SALES

COEFF

The material 4 have a coefficient of 1 but he doesn’t reliable with a KA.

Model

Loading

Edit

IF([Commercial document type code]='UB' OR [Commercial document type code]='NB','Y','N')

For Stocks BW query, we have one column of stock value by Month : [Measures_D_01] = M, [Measures_M_01]= M-1, [Measures_M_02]=M-2 … [Measures_M_23]=M-23

For Overdues BW query, we have one column of AR an Overdues value by Month : [Overdues_Previous_day], [Overdues_JAN_Y-1], [Overdues_FEB_Y-1], … [Overdues_DEC_Y]