Use Queries to select the group of parts you want to work with. You create a query either in the Conditions dialog box or you can write an SQL inspired query, we are talking about the WHERE Clause part of an SELECT statement. This will make it more flexible to find the parts you want to take a closer look at. It is also possible to get the query result aggregated so that you will get the Forecast Table and Forecast Graph show the aggregated result of the query. You can the manipulate all parts in the query the same way that you will manipulate a group selection. The other option is to list out all parts matching the query you can then click on one of the matching parts this will select this part and you can use the GUI to manipulate the forecast for this part. Below you can se witch operators and fields that are supported.
A unary operator uses only one operand. A unary operator typically appears with its operand in the following format:
operator operand
Operator | Explanation |
Unary + | Makes operand positive |
Unary - | Negates operand |
* | Multiplication |
/ | Division |
+ | Addition |
- | Subtraction |
< | Less than test |
<= | Less than or equal |
= | Equality test |
> | Greater than test |
>= | Greater than or equal test |
!= | Inequality test |
<> | Inequality test |
; or , | List operator, depends on the local settings of the PC |
LIKE | The LIKE operator is used to compare a value to similar values using wildcard operators. |
NOT | Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE |
AND | Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. |
OR | Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. |
TO_DATE | Converts from text to a date, TO_DATE(string1, 'format_mask') |
Supported TO_DATE format mask parameters
Parameter | Explanation |
YYYY | 4-digit year |
MM | Month (01-12; JAN = 01) |
DD | Day of week (1-7) |
HH24 | Hour of day (0-23). |
HH | Hour of day (1-12) |
MI | Minute (0-59). |
SS | Second (0-59). |
A.M. | Meridian indicator, before midday |
P.M. | Meridian indicator, after midday |
Query Name | In Demand Planning |
PART_NO | The part number |
PART_DESCRIPTION | The parts description |
FLOW_ID | The flow id |
FLOW_DESCRIPTION | The flows description |
FLOW_TYPE | base or combined flow |
CLASSIFICATION | The classification group of the part (new parts, intermittent, season, level...) |
SEASON_PROFILE_ID | The parts season profile (the id / number), see Detail View |
PERIOD PROFILE_ID | The parts period profile, see Detail View |
MOVABLE_HOLIDAY | The parts movable holiday, see Detail View |
APPROVED | If the part is approved for IPR or not |
ADJUSTMENT FACTOR | The parts adjustment factor. See Detail View |
ALPHA | The parts standard alpha value, see Detail View |
BETA | The parts standard beta value, see Detail View |
RHO | The parts standard rho value, see Detail View |
DELTA | The parts delta value, see Detail View |
FORECAST_MODEL | The parts standard forecast model, see Detail View |
HOLIDAY_MIXED_FACTOR | The parts holiday mixed factor. See Detail View |
PERIODICAL_LEAD_TIME | The parts periodical lead time, see Detail View |
FORECAST_COMMENT | The parts forecast comment, see forecast comment |
MANUAL_YEARLY_FORECAST | The parts standard manual yearly, see Detail View |
MOV_AV_PER | The parts standard moving average periods, see Detail View |
TRACKING_SIGNAL | The parts tracking signal, see Detail View |
PREDECESSOR | The parts predecessor. See Detail View |
INHERITANCE | The parts inheritance setting. See Detail View |
INHERITANCE_START | The parts inheritance start period. See Detail View |
PHASE_IN_LENGTH | The parts phase in length. See Detail View |
PHASE_IN_START | The parts phase in start. See Detail View |
PHASE_OUT_LENGTH | The parts phase out length. See Detail View |
PHASE_OUT_STOP | The parts phase out stop. See Detail View |
INHERITANCE_STOP | The parts inheritance stop period. See Detail View |
INHERIT_PERIOD_PROFILE | The parts setting of the period profile inheritance flag (DNA icon). See Detail View |
INHERIT_SEASON_PROFILE | The parts setting of the season profile inheritance flag (DNA icon). See Detail View |
INHERIT_WEEKLY_PROFILE | The parts setting of the weekly profile inheritance flag (DNA icon). See Detail View |
INHERIT_MOVABLE_HOLIDAY | The parts setting of the movable holiday inheritance flag (DNA icon). See Detail View |
ADJUSTMENT FACTOR | The parts adjustment factor. See Detail View |
MAE | The parts MAE (mean absolute error), see Detail View |
MAE_COEFFICIENT | The pars MAE coefficient, how large proportion of the average historical forecast (in the same interval as the forecast error measures are computed) is the MAE of the part. |
MAPE | The parts MAPE (mean absolute percentage error), see Detail View |
ME | The parts ME (mean error), see Detail View |
RME | The parts RME (Relative Mean Error), see Detail View |
MAE_VALUE | The parts Value of MAE, see Detail View |
PVE | The parts PVE (percent variation explained), see Detail View |
MSE | The parts MSE (mean squared error), see Detail View |
ADJUSTMENT_FACTOR | The pars adjustment factor, see Detail View |
WMAPE | The parts WMAPE (weighted mean absolute percentage error), see Detail View |
EXPECTED_DEMAND_SIZE | The parts expected demand size, see Detail View |
INTER_ARRIVAL_TIME | The parts inter arrival time, see Detail View |
PERCENT_USED | How much of the planned forecasted quantity that is sold until now in the period. See Aggregate Daily |
INVENTORY_VALUE | The parts inventory value / cost price, see Detail View |
INVENTORY_UNIT | The parts inventory unit, see Detail View |
LEAD_TIME | The parts lead time, see Detail View |
MARKET_SEGMENT | The parts market segment part, see Detail View |
PLANNED_SALES_PRICE | The parts sales price, see Detail View |
NET_WEIGHT | The parts net weight, see Detail View |
PART_FLOW_VALUE | The parts flow value, see Detail View |
CREATED_DATE | The date the part was created, see Detail View |
HISTORICAL_PERIODS | The length of the parts Demand vector since start until the current period, will only look at the parts demand, eventually inherited demand is not taken into consideration. |
TOTAL_HISTORICAL_PERIODS | The length of the parts Adjusted Demand vector since start until the current period. Will include the inherited demand as well |
TRACKING_SIGNAL | The parts tracking signal, see Detail View |
TRANSACTION_START_DATE | The parts transaction start date (sales start date), see Detail View |
MOVING_AVERAGE_PERIODS | The parts moving average periods, see Detail View |
CONFIRMED_PERIODS_AGO | Number of periods since the part was marked as confirmed |
CONFIRMED_DAYS_AGO | Number of days since the part was confirmed |
CONFIRMED_DATE | The date the part was confirmed |
CONFIRMED_STATUS | The status of the part confirmed or not confirmed |
FORECAST_CHANGED_BY | Which user has changed the forecast latest. See Detail View |
VARIANCE | The parts variance in demand, see Detail View |
STATUS | The parts status, see Detail View |
STATUS_CHANGED_BY | The user that changed the parts status last. |
STATUS_CHANGED_DATE | The date the parts status was changed. |
SUM_FORECAST | The sum of the parts adjusted forecast. |
MAE_RANGE (Length, Offset) |
The parts MAE (mean absolute error), see Detail View. This calculates the mean absolute error measurement for a specific time period. |
MAE_VALUE_RANGE (Length, Offset) | The parts Value of MAE, see Detail View. This calculates the Value of MAE error measurement for a specific time period. |
MAPE_RANGE (Length, Offset) | The parts MAPE (mean absolute percentage error), see Detail View. This calculates the mean absolute percentage error measurement for a specific time period. |
ME_RANGE (Length, Offset) | The parts ME (mean error), see Detail View. This calculates the mean error measurement for a specific time period. |
PVE_RANGE (Length, Offset) | The parts PVE (percent variation explained), see Detail View. This calculates the percent variation explained error measurement for a specific time period. |
RME_RANGE (Length, Offset) | The parts RME (Relative Mean Error), see Detail View. This calculates the Relative Mean Error measurement for a specific time period. |
WMAPE_RANGE (Length, Offset) | The parts WMAPE (weighted mean absolute percentage error), see Detail View. This calculates the weighted mean absolute percentage error measurement for a specific time period. |
VARIANCE_RANGE (Length, Offset) | The parts variance in demand, see Detail View . This calculates the variance error measurement for a specific time period. |
TRACKING_SIGNAL_RANGE (Length, Offset) | The parts tracking signal, see Detail View. This calculates the tracking signal error measurement for a specific time period. |
MAPE_RANGE (Length, Offset) | The parts MAPE (mean absolute percentage error), see Detail View. This calculates the mean absolute percentage error measurement for a specific time period. |
PREFORMANCE_FACTOR_RANGE (Length, Offset) | Forecast parts with the higher performance factor needs extra attention. Comparative to the demand variance, forecasted accuracy is low for such items. This calculates the performance factor error measurement for a specific time period. |
FILL_RATE_SERVICE_RANGE(Lenght, Offset) | Computes the fill rate service of the part see Detail View |
SERVICE_LEVEL(Length, Offset) | Computes the service (success) level of the part see Detail View |
In addition you can use any of the user selected groups that are using in a query, the field name of the group is then used as query name. If you use the any of the standard available groups these will be the names.
PART_PRODUCT_FAMILY
PART_PRODUCT_CODE
ABC_CLASS
TYPE_CODE_DB
LEAD_TIME_CODE_DB
PLANNER_BUYER
ASSET_CLASS
PRIME_COMMODITY
SECOUND_COMMODITY
DEMAND_MODEL
LIFECYCLE_STAGE
FREQUENCY_CLASS
And any other that you include yourself is available.
Note that for the fields Forecast Model, Season Profile, Period Profile and Weekly Profile you must use the ID / number when you write your query. For group fields with description you most use the ID for the group when writing the query.
Custom Fields: This allows user to add custom fields both in the detail view and query based on the users’ business requirements. In order to add custom fields, User needs to navigate to forecast_cust_field_ setup_tab and specify the following custom field related information in the table columns manually.
Table Column Name |
Description |
FORECAST_SERVER_ID |
The specific forecast server ID, where custom field is added. |
TABLE_VIEW_NAME |
The specific database table, where custom field is retrieved. |
FIELD_NAME |
The specific custom field that is retrieving from database |
DESCIPTION |
The specific custom field name that need to be appear in detail view and query. |
ROWVERSION |
The specific date and time, where user insert/update the custom field |
In order to appear the inserted/updated custom fields in the detail view and query, User needs to commit the changes done in the forecast_cust_field_ setup_tab and restart the demand plan server
Note- The user needs to run the POST_Demand_DemandServerUser.sql script, Once the changes have been committed in the forecast_cust_field_ setup_tab. (The script is stored in the following file location demand\source\demand\database\POST_Demand_DemandServerUser.sql ")