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

QV application : CRM Analytics

QV is updated daily (monday to friday) :

→ around 09.30 am CET with DynaSys data updated into BW at 11 pm previous day

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

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

Application

$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2018-7-31 16:7:10.png$

$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2018-7-31 16:8:22.png$

Description	BW - DP - Demand Review for SBS Dashboard (QV)
Technical Name	QVSBS_BW_QRY_MVDYN11_0001
Application	DP - Demand Planning
Info-provider	MultiProvider MVDYN11
Usage type	Source for Qlikview
Expected users	Ex: GBU zone and WW controllers

Workbook/Query General Description

In case of issue, the query has been saved by version

Code	Description	Date of copy on WBD
QVSBS_BW_QRY_MVDYN11_0001_SAV1	[SAV V1] BW - DP - Demand Review for SBS Dashboard (QV)	22/11/2018

General parameters

Global properties

Tab	Screenshot	Comment
Display	$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2016-11-4 16:16:26.png$
Rows/Columns	$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2016-11-4 16:16:51.png$
Value Display	$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2016-11-4 16:17:18.png$
Planning	$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2016-11-4 16:17:48.png$
Extended	$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2016-11-4 16:18:11.png$

Variables

Definitions

Variable Name	Info-object	Selection Type	Required	Description/Explanation
CALMONT2 Calendar Month/Year (Interval, Mandatory)	0CALMONTH	Interval/Mandatory/ManualEntry	Yes
V_CPFCTR1_2_0008 Global Business Unit (Selection Option, Optional, Auth)	CPFCTR1_2	SelectionOption/Optional/Authorization/ManualEntry	No
V_C_MATNR2_0001 Material (Selection option)	C_MATNR2	SelectionOption/Optional/ManualEntry	No
CPROD01 Commercial Product (Select option, Optional)	C_MATNR2__C_PROD	SelectionOption/Optional/ManualEntry	No
VPROD_HIER Product Hierarchy (Selection option, optional)	C_MATNR2__0PROD_HIER	SelectionOption/Optional/ManualEntry	No
V_CUSTID_0002 Ship-to (Selection option)	C_SHIPID	SelectionOption/Optional/ManualEntry	No	Previously it was C_SHIPTO but description were not daily updated.
V_0DISTR_CHAN_0001 Distribution Channel (Selection option, Optional)	0DISTR_CHAN	SelectionOption/Optional/ManualEntry	No
V_C_GBR6_0001 / GBR Customer Group (Select Option, Optional)	GBR Customer Group (C_GBR13__GBR6)	SelectionOption/Optional/ManualEntry	No	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_GRCUST	SelectionOption/Optional/ManualEntry	No	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/ManualEntry	No	Sales Group = Sales Rep = only for RCS
V_C_GZONE Geo Zone (Selection option, Optional)	C_SHTCTRY__C_GZONE	SelectionOption/Optional/ManualEntry	No	From Ship-to Country
V_C_PLANT_0010 Plant (Selection Option, Optional)	C_DYN_018__C_MPPLANT	SelectionOption/Optional/ManualEntry	No

Variable Sequence

$TECHNO - BI TEAM > QV - Demand Review for SBS Dashboard - All except CM (Aero) /!\ Obsolete /!\ > image2017-5-9 17:24:47.png$

Filters

Object

Filter Value

Explanation

InfoProvider (0INFOPROV)

= DYN Reporting / CRDYN11

= LOG - Reporting (Dynamic) / CRLOG01

= APO - Global (Dynamic) / CRAPO002

SnapShot cubes will be removed soon.

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

Will be changed soon Filter on M only.

Flag Prospect (C_DYN_024__C_PROSP)

<> X 'Prospect'

Exclude SF Prospects from DynaSys

Key figures

All keyfigures without decimals as in Dynasys

Description	type	Calculation rule	Conversion	Comments
~~FF Quantity M-1~~	~~Display~~	~~Ratio K_FFM1QTY~~	~~QCT_MATNR5~~	Removed 18/12/2017
Final Forecast Quantity ETA/ETD	Display	Ratio K_FFQTY	QCT_MATNR5	Renamed 26/11/2018 (previous FF Quantity)
Final Forecast Amount ETA/ETD (EUR)	Display	Ratio K_FCSTAMT	CTK_CAR4_M Target curr = EUR	Logility + DynaSys (since 19/10/2017) Translation key ZCAR4 -Begin of month Renamed 26/11/2018 (previous Forecast Amount (EUR)))
Final Forecast Amount ETA/ETD (USD)	Display	Ratio K_FCSTAMT	CTK_CAR4_M Target curr = USD	Logility + DynaSys (since 19/10/2017) Translation key ZCAR4 -Begin of month Renamed 26/11/2018 (previous Forecast Amount (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_CONFOR	QCT_MATNR5	Added 26/11/2018
Constrained Forecast Amount (EUR)	Display	Ratio K_BFR3EUR	CTK_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_DYSTF	QCT_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

Characteristics

Location	Info-object Name	Info-object Text	Explanation (if needed)
Free	0CALMONTH	Calendar Year/Month	Text
Rows	0LOGSYS	Source System	No display to avoid the display of the prefix for compound characteristics.
Rows	C_MATNR2	Material	Key + Text
Rows	C_SHIPTID	Ship-to	Key + Text With attributes in display mode: C_GRCUST - Customer Group
Rows	CPFCTR1_2	BFC 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
Free	C_MATNR2__0PROD_HIER	Prod. Hierarchy	Key + Text
Free	C_MATNR2__C_PROD	Commercial Product	Key + Text Commercial Product is only available for RCS WP1. Do not exists for SOLVAY PF1.
Free	C_CUSTGP2	Enterprise Customer Group (APO)	Key + Text Only APO since 19/10/2017
Free	C_SHTCTRY	Ship-to Country	Text
Free	C_SOLDID__C_GRCUST	Industrial 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.
Free	C_SOLDID	Sold-to	Key + Text Previously, the Sold-to was C_SOLDTO, but description were not daily loaded.
Free	C_DYN_017__C_SAL_GRP	Sales Group	Sales Group = Sales Rep = Only for RCS
Free	C_SHTCTRY__C_GZONE	Geo Zone	No description available
Free	C_CUSTOMR__0REGION	Ship-to Region
Free	C_MATNR2__C_MAT_GRP	Material Group
Free	C_MATNR2__C_MATGR2	Packaging Type	Previous name : Material Group 2 (Release March 2017) For Solvay PF1 only
Free	C_DYN_010	BU
~~Free~~	~~C_DYN_018__C_INCOTRM~~	~~Incoterms~~	~~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)~~
Free	C_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
Free	C_IECRA2	IECRA	APO only
Free	C_GBR14__C_GBR1	Application	Old C_GBR5__C_GBR1 since 19/10/2017
Free	C_GBR14__C_GBR4	Corporate End Use	Old C_GBR5__C_GBR4 since 19/10/2017
Free	C_GBR14__C_GBR2	Market	Old C_GBR5__C_GBR2 since 19/10/2017
Free	C_GBR14__C_GBR3	Segment	Old C_GBR5__C_GBR3 since 19/10/2017
Free	C_PLANT	Plant
Free	C_MATNR2__C_FMPRD	Product Family Code
Free	C_CUSTOMR__0COUNTRY	Cust: Country
Free	C_DYN_018__C_COMPCDE	Company code	with following attributes in display mode: 0COUNTRY - Country key C_MNGCTRY - Management Country C_GZONE - Geographie/Zone C_ZONE - BFC Geographie/Zone
Free	C_DOCTYP2	Sales Document Type	value DYN, APO, LOG => will be soon available
Free	C_GBR15__C_SHIPKA	Ship-to KA (GBR)	for DYN, APO. Available since 19/10/2017
Free	C_SHTCTRY__C_ZONE	BFC Geographie/Zone	Available since 19/10/2017
Free	C_DYN_024	Ship-to (incl.Prosp)	For DYN, new axis Ship-to including Sales Force prospects => Available since 19/10/2017
Free	0SALES_GRP	Sales Group (Log)	Available since 12/03/2018 for QV reporting.
Free	C_GBR13_C_GBR6	GBR GBU group (Ship-to)	Available since 23/07/2018 for QV reporting.
Free	C_GBR18_C_GBR20	GBR GBU Mat Sub-grp	Available since 23/07/2018 for QV reporting.
Free	C_GBR18_C_GBR19	GBR GBU Mat Group	Available since 23/07/2018 for QV reporting.
Free	C_GBUZONE__C_ZONEH2	GBU Zone (hier. 2)	Available since 23/07/2018 for QV reporting.
Free	C_GBUZONE__C_ZONEH1	GBU Zone (hier. 1)	Available since 23/07/2018 for QV reporting.
Free	C_MATNR2_C_LIP02	Product Line 02	Available since 23/07/2018 for QV reporting.
Free	C_MATNR2_C_LIP03	Product Line 03	Available since 23/07/2018 for QV reporting.
Free	C_MATNR2_C_LIP04	Product Line 04	Available since 23/07/2018 for QV reporting.
Free	C_DYN_005_C_TACTIC2	Tactical Material	Available since 23/07/2018 for QV reporting.
Free	C_DYN_017_C_ZIPART	CSO	Available since 23/07/2018 for QV reporting.
Free	C_CUSTOMR_0CITY	Ship-to City	Available since 23/07/2018 for QV reporting.
Free	C_DYN_018_C_MPPLANT	Main Shipping Plant	Available since 23/07/2018 for QV reporting.
Free	C_MATNR2_C_MAGRV	Packaging Materials	Available since 23/07/2018 for QV reporting.
Free	C_DYN_018_C_VEPART	Account Manager	Available since 23/07/2018 for QV reporting.
Free	C_DYN_018_C_VWPART	CCS 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. Changes to C_DYN_21 27/11/2018
Free	C_DYN_021_C_DYN_022	ABC Class (auto)	Available since 23/07/2018 for QV reporting.
Free	C_DYN_021_C_DYN_023	ABC Class (manual)	Available since 23/07/2018 for QV reporting.
Free	C_SOTCTRY_C_ZONE	BFC Geographie/Zone	Available since 23/07/2018 for QV reporting.
Free	C_DYN_C_PROSP	Flag Prospect	Available since 23/07/2018 for QV reporting.
Free	C_DYN_018__C_PRDFACT	Default Prod Factory	Available since 23/07/2018 for QV reporting.
Free	C_DYN_005__C_GRPPGMI	Product Group (PGMI)	Available since 23/07/2018 for QV reporting.
Free	C_MATNR2_C_FMPRD	Product Family	Available since 23/07/2018 for QV reporting.
Free	C_SOTCTRY	Sold-to Country	Available since 23/07/2018 for QV reporting.
Free	C_SOTCTRY__C_Zone	Sold-to BFC Geographie/Zone	Available since 23/07/2018 for QV reporting.
~~Free~~	~~C_DYN_018__C_INCOTM2~~	~~Incotems 2~~	~~Available since 23/07/2018 for QV reporting.~~
Free	C_DYN_018__C_INCOTRM	Incoterms (Solvay)	Key ; Available since 18/10/2018
Free	C_DYN_018__C_INCOTM2	Incoterms 2 (Solvay)	Key ; Available since 18/10/2018
Free	C_DYN_017__C_INCOTRM	Incoterms (RCS)	Key ; Available since 18/10/2018
Free	C_DYN_017__C_INCOTM2	Incoterms 2 (RCS)	Key ; Available since 18/10/2018

Cells

If you have 2 structures and special cells calculations, explain here.

Conditions

If you have conditions, explain here.

Condition Name

Active

By default

Key Figure

(Structure)

Operator

Value

Characteristic Assignement

Ex : Excl if <=10

Yes/No

Sales Value

Greater Than

10

Ex : All Independently

Exceptions

If you have conditions, explain here.

Exception Name

Active

By default

Alert Level

Operator

Value

Other parameters

(if different from default)

Ex : Red if <=10

Yes/No

Bad 3

Lesser Than

10