Versions Compared

Old Version 9

changes.mady.by.user PERRILLAT-COLLOMB, Nathalie

Saved on

compared with

New Version Current

changes.mady.by.user GIRAUD-ext, Mickael

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

(warning)     (warning)     (warning)     (warning)

The new wiki link for this data flow is here:

QV - Demand Review for SBS Dashboard - All except CM (Aero) 

Please update the new doc there and no longer here.

(warning)    (warning)     (warning)     (warning)




This query was This query is used to provide Qlik View Forecast informations from APO / DynaSys and Logility.

QV application : CRM Analytics

DynaSys (Application CRM Analytics )

It is now used for Global Supply Chain (tableau) + Customer 360 (QlikSense ?)



see also DynaSys doc 

Embedded Google Drive File
worksheetsSummary Export/Refresh time
docid1EABK8bhuYtho0SvqweVQoXJpKg9nuCHP1you6H4GM70
width30%

 

To access QV : qlikview.solvay.com => Applications Global Supply Chain Dashboard will be accessible up to 2023 July 31th

To access Tableau : https://tableau.solvay.com => Global Supply Chain Dashboard here 


QV was updated daily (monday to friday) (I do not know if it is still the case for QlikSens or Tableau)

→ around 7.15 am CET with  DynaSys data updated into BW at 4 am  (QV launched at 5 am and last 2 hours)

→ around 5.15 pm CET with  DynaSys data updated into BW at 2 pm  (QV launched at 3 pm and last 2 hours) 


Description

BW - DP - Demand Review for SBS Dashboard (QV)

Technical NameQVSBS_BW_QRY_MVDYN11_0001

Application

DP - Demand Planning

Info-provider

MultiProvider MVDYN11

Usage type

Source for Qlikview

Expected users


Table of Contents
maxLevel3

Workbook/Query General Description


Few links to QV document to better understand CRM Analytics application

Dimensions Overview for Analytics Dashboard

Embedded Google Drive File
docid1QV_kuPycWggirXeKlrJO0-voBux0cFWVUfkRtJYNJDY


CRM Analytics - Demand Forecast

Embedded Google Drive File
docid1VWiCLF8EMFZuSNl22sSaKe1-sduM6HGBnTVqXTCL1d8

General parameters

Global properties

TabScreenshotComment
Display
Image Modified
 
Rows/Columns
Image Modified
 
Value Display
Image Modified
 

Planning
Image Modified
 
Extended
Image Modified
 

Variables

Definitions

Variable Name

Info-object

Selection Type

Required

Description/Explanation

CALMONT2

Calendar Month/Year (Interval, Mandatory)

0CALMONTHInterval/Mandatory/ManualEntryYes 

V_CPFCTR1_2_0008

Global Business Unit (Selection Option, Optional, Auth)

CPFCTR1_2SelectionOption/Optional/Authorization/ManualEntryNo 

V_C_MATNR2_0001

Material (Selection option)

C_MATNR2SelectionOption/Optional/ManualEntryNo 

CPROD01

Commercial Product (Select option, Optional)

C_MATNR2__C_PRODSelectionOption/Optional/ManualEntryNo 

VPROD_HIER

Product Hierarchy (Selection option, optional)

C_MATNR2__0PROD_HIERSelectionOption/Optional/ManualEntryNo 

V_CUSTID_0002

Ship-to (Selection option)

C_SHIPIDSelectionOption/Optional/ManualEntryNoPreviously it was C_SHIPTO but description were not daily updated.

V_0DISTR_CHAN_0001

Distribution Channel (Selection option, Optional)

0DISTR_CHANSelectionOption/Optional/ManualEntryNo 

V_C_GBR6_0001 / GBR Customer Group (Select Option, Optional)

GBR Customer Group (C_GBR13__GBR6)SelectionOption/Optional/ManualEntryNo

Ship-to info

Previous 19/10/2017

 

V_C_CUSTGP2_0001

 

Enterprise Customer Group (Section option, Optional)


V_0COUNTRY_0003

Ship-to Country (Selection option, Optional)

C_SHTCTRY

SelectionOption/Optional/ManualEntry

No

 

 



V_C_GRCUST_0001

Industrial Group (Selection option, Optional)

C_SOLDID__C_GRCUSTSelectionOption/Optional/ManualEntryNo

Sold-to info

V1 => Ship-to info (C_CUSTOMR__C_GRCUST)

V_C_SAL_GRP

Sales Group (Selection option, Optional)

C_DYN_017__C_SAL_GRP

SelectionOption/Optional/ManualEntryNoSales Group = Sales Rep = only for RCS

V_C_GZONE

Geo Zone (Selection option, Optional)

C_SHTCTRY__C_GZONESelectionOption/Optional/ManualEntryNoFrom Ship-to Country

V_C_PLANT_0010

Plant (Selection Option, Optional)

C_DYN_018__C_MPPLANTSelectionOption/Optional/ManualEntryNo 

Variable Sequence

Image Modified

Filters

ObjectFilter ValueExplanation
InfoProvider (0INFOPROV)

= DYN Reporting / CRDYN11

= LOG - Reporting (Dynamic) / CRLOG01

= APO - Global (Dynamic) / CRAPO002

SnapShot cubes will be removed soon.

09.2020 APO cube removed (not filled anymore since Special Chem go-live)Only DynaSys Snapshot remains are there are in the same cube than DR data


Concerned Period (C_FLGMTH)= 'M' and 'M-1'

Only current values are requiered

Flag Prospect (C_DYN_024__C_PROSP)<> X 'Prospect'Exclude SF Prospects from DynaSys
 


Key figures

All keyfigures without decimals as in Dynasys

DescriptiontypeRestricted or Calculated KeyFigureCalculation ruleConversionComments

FF Quantity M-1

Display Ratio K_FFM1QTYQCT_MATNR5 Removed 18/12/2017

Final Forecast Quantity ETA/ETD

FF Quantity

Display Ratio K_FFQTYQCT_MATNR5

 Renamed 26/11/2018 (previous FF Quantity)

Removed 18/08/2021

Final Forecast Quantity ETD

Display
Ratio K_DYN_034QCT_MATNR5 Added 18/08/2021

Final Forecast Amount (EUR)

Display Ratio K_FCSTAMT

CTK_ZRHOMCAR4_M

Target curr = EUR

Logility + DynaSys (since 19/10/2017)

Translation key ZRHO ZCAR4 - End of monthBegin of month

 Renamed 26/11/2018 (previous Forecast Amount (EUR)))

Renamed 18/08/2021 Previous (Final Forecast Amount ETA/ETD (EUR)

Final Forecast Amount (USD)

Display Ratio K_FCSTAMT

CTK_CAR4_ZRHOMM

Target curr = EURUSD

Logility + DynaSys (since 19/10/2017)

Translation key ZRHO ZCAR4 - End of monthBegin of month

 Renamed 26/11/2018 (previous Forecast Amount (USD))

Renamed 18/08/2021 Previous (Final Forecast Amount ETA/ETD (USD)

Forecast Amount (doc curr)Display Ratio K_FCSTAMT

no conversion

Logility + DynaSys (since 19/10/2017)

Removed 26/11/2018

Constrained Forecast Quantity

Display Ratio K_CONFORQCT_MATNR5Added 26/11/2018

Constrained Forecast Amount (EUR)

Display
Ratio K_BFR3EUR

CTK_ZRHOM

Target curr = EUR

Logility + DynaSys (since 19/10/2017)

Translation key ZRHO - End of month

 

_CAR4_M

Target curr = EUR

Added 26/11/2018

Constrained Forecast Amount (USD)

Display
Ratio K_BFR3EUR

CTK_CAR4_M

Target curr = USD

Added 26/11/2018

Sales Team Forecast Quantity

Display Ratio K_DYSTFQCT_MATNR5

Added 26/11/2018


Sales Team Forecast Amount (EUR)

Display
Ratio K_DYN_031

CTK_CAR4_M

Target curr = EUR

Added 26/11/2018

Sales Team  Forecast Amount (USD)

Display
Ratio K_DYN_031

CTK_CAR4_M

Target curr = USD

Added 26/11/2018

Final Forecast Amount (LC)

Display
Ratio K_DYN_072Target Curr = Local Currency

Added 10/12/2018

renamed 18/08/2021 previous "Final Forecast Amount ETA/ETD (LC)"

Sales Team Forecast Amount (LC)Display
Ratio K_STSALESTarget curr = Local CurrencyAdded 10/12/2018
BudgetDisplay
K_DYBUD / 0UNITin KgAdded 29/08/2019
Budget in €Display
K_BUDEURno conversion, already in €Added 29/08/2019
Budget Net Sales ($)Display
K_DYN_066no conversion, already in $Added 29/08/2019
Budget Net Sales LCDisplay
K_DYN_0124no conversionAdded 29/08/2019
Budget Contribution MarginDisplay
K_DYN048no conversion, already in €Added 29/08/2019
Budget Contribution Margin ($)Display
K_DYN_064no conversion, already in $Added 29/08/2019
BFR06Display
K_DYBFR06in KgAdded 29/08/2019
BFR06 in €Display
K_BFR6EURno conversion, already in €Added 29/08/2019
BFR6 Net Sales ($)Display
K_DYN_067no conversion, already in $Added 29/08/2019
BFR06 Net sales LCDisplay
K_DYN_125
Added 29/08/2019
BFR6 Contribution MarginDisplay
K_DYN_032no conversion, already in €Added 29/08/2019
BFR6 Contribution Margin ($)Display
K_DYN_068no conversion, already in $Added 29/08/2019
Projected ICM (€)Display
Ratio K_DYN_046 Final Forecast Contribution Margin

no conversion, already in €

Added 08/10/2019

Projected ICM Current Month (€)


Display
Ratio K_DYN_129 Current Month Contribution Marginno conversion, already in €Added 08/10/2019
Projected ICM (USD)Display
Ratio K_DYN_046 Final Forecast Contribution Margin

CTK_CAR4_M

Target curr = USD

Added 12/11/2019
Projected ICM Current Month (USD)Display
Ratio K_DYN_129 Current Month Contribution Margin

CTK_CAR4_M

Target curr = USD

Added 12/11/2019


Characteristics

LocationInfo-object NameInfo-object TextExplanation (if needed)
ColumnFree0CALMONTHCalendar Year/MonthText
Rows0LOGSYSSource SystemNo display to avoid the display of the prefix for compound characteristics.
RowsC_MATNR2Material

Key + Text

RowsC_SHIPTIDShip-to

Key + Text

With attributes in display mode: C_GRCUST - Customer Group

FreeRowsCPFCTR1_2BFC Global Business Unit

Key + Text

From DSO DBDYN02 to get the BU (C_DYN_010), then MasterData C_DYN_010 to get the GBU

12/03/2018 → Field now filled from Logility flow

FreeC_MATNR2__0PROD_HIERProd. HierarchyKey + Text
FreeC_MATNR2__C_PRODCommercial Product

Key + Text

Commercial Product is only available for RCS WP1. Do not exists for SOLVAY PF1.

FreeC_CUSTGP2Enterprise Customer Group (APO)

Key + Text

Only APO since 19/10/2017


FreeC_SHTCTRYShip-to CountryText
FreeC_SOLDID__C_GRCUSTIndustrial Group

Text

V1 = C_CUSTOMR__C_GRCUST

For V2 it has been changed for C_CUSTID. The field KONZS is taken from PRS Customer. PRS seems not contains all customers (only payer?). If Customer Group (C_GRCUST) are missing, the masterdata will have to be upgraded to get missing ones.

FreeC_SOLDIDSold-to

Key + Text

Previously, the Sold-to was C_SOLDTO, but description were not daily loaded.

FreeC_DYN_017__C_SAL_GRPSales GroupSales Group = Sales Rep = Only for RCS
FreeC_SHTCTRY__C_GZONEGeo ZoneNo description available
FreeC_CUSTOMR__0REGIONShip-to Region 
FreeC_MATNR2__C_MAT_GRPMaterial Group 
FreeC_MATNR2__C_MATGR2Packaging Type

Previous name : Material Group 2 (Release March 2017)

For Solvay PF1 only

FreeC_DYN_010BU 
FreeC_DYN_018__C_INCOTRMIncoterms

Available since 07/02/2017

Incoterms of the last M3 sales order not rejected

(No reason for Rejection , Item Category M3N, most recent change on date)

FreeC_DYN_018__C_DYN_016

Key Account

(Ship-to KA) (APO)

RCS notion

Text in MD C_GRCUST

Available since 14/03/2017

Only APO since 19/10/2017

Will be removed soon. Use GBU one => item 480

FreeC_IECRA2IECRAAPO only
FreeC_GBR14__C_GBR1Application

Old C_GBR5__C_GBR1 since 19/10/2017

FreeC_GBR14__C_GBR4Corporate End Use

Old C_GBR5__C_GBR4 since 19/10/2017

FreeC_GBR14__C_GBR2Market

Old C_GBR5__C_GBR2 since 19/10/2017

FreeC_GBR14__C_GBR3Segment

Old C_GBR5__C_GBR3 since 19/10/2017

FreeC_PLANTPlant 
FreeC_MATNR2__C_FMPRDProduct Family Code 
FreeC_CUSTOMR__0COUNTRYCust: Country 
FreeC_DYN_018__C_COMPCDECompany code

with following attributes in display mode:

  • 0COUNTRY - Country key
  • C_MNGCTRY - Management Country
  • C_GZONE - Geographie/Zone
  • C_ZONE - BFC Geographie/Zone
FreeC_DOCTYP2Sales Document Typevalue DYN, APO, LOG => will be soon available
FreeC_GBR15__C_SHIPKAShip-to KA (GBR) for DYN, APO. Available since 19/10/2017
FreeC_SHTCTRY__C_ZONEBFC Geographie/ZoneAvailable since 19/10/2017
Free C_DYN_024Ship-to (incl.Prosp) For DYN, new axis Ship-to including Sales Force prospects => Available since 19/10/2017
Free0SALES_GRPSales Group (Log)

Available since 12/03/2018 for QV reporting.

Removed 10/12/2018

FreeC_GBR13_C_GBR6GBR GBU group (Ship-to)Available since 23/07/2018 for QV reporting.
FreeC_GBR18_C_GBR20GBR GBU Mat Sub-grpAvailable since 23/07/2018 for QV reporting.
FreeC_GBR18_C_GBR19GBR GBU Mat GroupAvailable since 23/07/2018 for QV reporting.
FreeC_GBUZONE__C_ZONEH2GBU Zone (hier. 2)  Available since 23/07/2018 for QV reporting.
FreeC_GBUZONE__C_ZONEH1GBU Zone (hier. 1)Available since 23/07/2018 for QV reporting.
FreeC_MATNR2_C_LIP02Product Line 02Available since 23/07/2018 for QV reporting.
FreeC_MATNR2_C_LIP03Product Line 03Available since 23/07/2018 for QV reporting.
FreeC_MATNR2_C_LIP04Product Line 04Available since 23/07/2018 for QV reporting.
FreeC_DYN_005_C_TACTIC2Tactical Material  Available since 23/07/2018 for QV reporting.
FreeC_DYN_017_C_ZIPARTCSO                                     Available since 23/07/2018 for QV reporting.
FreeC_CUSTOMR_0CITYShip-to CityAvailable since 23/07/2018 for QV reporting.
FreeC_DYN_018_C_MPPLANTMain Shipping Plant Available since 23/07/2018 for QV reporting.
FreeC_MATNR2_C_MAGRVPackaging Materials Available since 23/07/2018 for QV reporting.
FreeC_DYN_018_C_VEPARTAccount Manager  Available since 23/07/2018 for QV reporting.
FreeC_DYN_018_C_VWPARTCCS REP Available since 23/07/2018 for QV reporting.
Free

C_DYN_019_C_DYN_020

C_DYN_021__C_DYN_020

Planning Zone

Available since 23/07/2018 for QV reporting.

Changed to C_DYN_21 27/11/2018

FreeC_DYN_021_C_DYN_022ABC Class  (auto)    Available since 23/07/2018 for QV reporting.
FreeC_DYN_021_C_DYN_023ABC Class  (manual) Available since 23/07/2018 for QV reporting.
FreeC_SOTCTRY_C_ZONEBFC Geographie/Zone Available since 23/07/2018 for QV reporting.
Free C_DYN_C_PROSPFlag Prospect Available since 23/07/2018 for QV reporting.
FreeC_DYN_018__C_PRDFACT Default Prod Factory Available since 23/07/2018 for QV reporting.
FreeC_DYN_005__C_GRPPGMIProduct Group (PGMI)  Available since 23/07/2018 for QV reporting.
FreeC_MATNR2_C_FMPRD Product FamilyAvailable since 23/07/2018 for QV reporting.
FreeC_SOTCTRYSold-to Country Available since 23/07/2018 for QV reporting.
FreeC_SOTCTRY__C_Zone  Sold-to BFC Geographie/Zone Available since 23/07/2018 for QV reporting.
FreeC_DYN_018__C_INCOTM2Incotems 2  Available since 23/07/2018 for QV reporting.
FreeC_DYN_018__C_INCOTRMIncoterms (Solvay)

Key ; Available since 18/10/2018

FreeC_DYN_018__C_INCOTM2Incoterms 2 (Solvay)

Key  ; Available since 18/10/2018

FreeC_DYN_017__C_INCOTRMIncoterms (RCS)Key  ; Available since 18/10/2018
FreeC_DYN_017__C_INCOTM2Incoterms 2 (RCS)Key  ; Available since 18/10/2018

Cells

Conditions

 
Condition Name

Active

By default

Key Figure

(Structure)

OperatorValueCharacteristic Assignement     






Exceptions

Exception Name

Active

By default

Alert Level

OperatorValue

Other parameters

(if different from default)

Yes/No