Skip to main content

Import Forecasts

Updated

The Import Forecasts screen allows you to bulk-import forecast quantities into StockIQ.  This is accomplished by uploading a specially formatted file with the forecast values that you want.

Each line of the forecast file represents one period for a particular hierarchy node, such as January-2020 for a particular item-site-forecast-group combination.

You can access this screen by going to Forecast --> Import Forecasts

NOTE: There is an extensive training video in the Support --> Videos section for the import forecasts screen that provides a tutorial on forecast uploading.

 

Step 1: Upload Your File

The first step in uploading a forecast is to create your file and upload it.  At the bottom of the screen 

 

Import Configuration

 

There are several options for how StockIQ should interpret the file you provide:

  • Import Format - Specifies the format StockIQ is expecting. 
    • "Horizontal" style layout has item information in the first few columns, with quantities by date laid out left-to-right in a single row. 
    • "Vertical" is a more relational/table style layout, with each row representing a single value for your forecast.
  • Import Interval - sets the expected interval for a horizontal import.
  • Import UoM - sets the expected UoM for a horizontal import, e.g. units, Revenue, COGS, etc.
  • Merge Mode - Specifies how values in your import should be integrated with existing values.  Should StockIQ:
    • Replace - replaces any values previously there
    • Add - Add the uploaded value to the existing value.
  • Zero-Missing Period Mode - this selection tells StockIQ what to do with any periods that are not specified in your forecast file:
    • Preserve Old Value - This means that StockIQ will keep whatever is in place from your forecast if you didn't specify a value explicitly.  This is the most common, and the safest option.
    • Zero Future - Any periods NOT in your forecast upload from current period forward will be zeroed out.  Use this option only if you want to reset ALL existing forecast for a given item.  Use this option very carefully!
    • Zero Future and Historical - This is the nuclear option, which will wipe out any forecast data for a given item not present in your forecast upload.  Use this option very carefully!
  • Auto-Forecast - Specifies what to do with the auto-forecast setting on the Forecast Settings Tab for the affected item.  
    • Do Nothing - no changes to your existing forecast settings.  If you have existing manual forecast settings this might make sense, but if you have an auto-forecast, this option would mean your new manual forecast would not take effect, since no override would be saved.  So,this option is not commonly used, it's only for certain scenarios. 
    • Disable - Disables auto-forecasting.  The item will use the manually uploaded forecast until modified by the user or set back to auto-forecasting.
    • Restart On Date - Set auto forecast to resume on a date you specify
    • Restart on Last Date Found - StockIQ will set the auto-forecast resume date to the next period AFTER the last one found in the file.  So, if you upload a forecast for Jan-Mar for a given item, the auto-forecast resume date would be set to April 1st.
    • Enable - Keeps auto-forecasting enabled.  Typically only used if you are uploading some forecast history.  Not commonly used.
  • Data Import Level - Specifies at which level you are intending to import data, e.g. is this a bottom-up, top-down, or middle-down import?  Based on what you select, SIQ will show you a tip on which columns of the import you need to populate for StockIQ to be able to definitively know which spot in the hierarchy each value in your spreadsheet matches.
  • Ignore Header Row - If your file has row headers, enable this option to have StockIQ ignore the header row.
  • Create New/Missing Nodes - If you wish to be able to upload into a new hierarchy entry, such as for a brand new forecast on a brand new item with no sales, or a new customer, or new customer channel/forecast group then check this checkbox.  Un-checking this box protects against bad data entry in the source spreadsheet thus preventing new forecast nodes from being created in error.   Tip: If you check this the next refresh may run longer because it forces a re-calculation of Cost/Price.  To prevent the longer refresh check the box below to run the calculations at the time of import. 
  • Set Cost/Price info for new nodes (slower) - When the Create New/Missing Nodes is checked you should consider checking this box to calculate the Cost/Price for the new and related nodes.  While it may slow down processing at the time of import it may save significant time when refreshing as some clients refresh time can be extended up to 2X longer based on these calculations.  
  • Cancel Import If Errors Found Parsing File - StockIQ will cancel the import and not save ANY values if errors are found when parsing the imported data into staging table.
  • Cancel Import If Errors Found - StockIQ will cancel the import and not save ANY values if errors are found when reading the imported data into the database.
  • Update Historical Values - Enable this option if you wish to update forecast history, e.g. forecast values that are in the past.  This is not common.
  • Apply Imported Note to Forecast Settings - StockIQ saves a note on the imported forecast itself.  Additionally, when upload a forecast, very often the forecast settings must be updated as well, e.g. to flip the item to manual forecasting.  When this is the case, StockIQ can copy any notes saved in the forecast to the forecast settings as well, so they are easily visible in Forecast Manager.
  • Override Child Forecasts and Settings? - This specifies whether any top-down or middle-down values import should 100% override any child settings or overrides.  When checked, any child forecast settings or overrides will be removed and replaced with the new top-down forecast.

Step 2: Import Preview

Once you have  uploaded your file, then click "Next."  StockIQ will summarize what it has uploaded in a grid so you can make sure your file was interpreted correctly.

A summary of the import rules you selected will be shown at the bottom.

Click Next to start the actual import of data.

Step 3: Import Data

StockIQ will work for a bit and import your data.  Any error messages will appear in the grid below, and a summary is displayed at the top.  Your forecast data should be immediately available and visible in Forecast Manager.

 

Common Error Codes

Below are a few common error codes that we see when folks upload files.  If you're having trouble, make sure that you haven't run afoul of any of these:

  • Could not find an existing hierarchy match for this record. May need to enable the 'Create New Nodes' Option? - This means that the forecast you are trying to upload is for a new item-site combo that does not exist on SIQ yet. Check 'Create New/Missing Nodes' on the forecast import screen and try again. 
  • Record has missing or empty 'CustomerShipToCategory1Code', and this is a required field for your import - Depending on the Data Import level you select, this field may be required for your import. If you are wondering what your "CustomerShipToCategory Code" is, you can find it at System configuration - -> Customer-Ship-To Category Settings.

Common Mistakes

Below are a few common mistakes that we see when folks upload files.  If you're having trouble, make sure that you haven't run afoul of any of these:

  • Deleting columns from the import template on the forecast import screen - If you are not filling out data in a column, please leave the column blank and as is while importing. The import will error out if any columns are removed.
  • Importing forecast at the wrong "Data import level" - Additional columns may appear in the csv format file, depending on the data import level selected for the forecast import. Be sure to select the data import level at which you want the forecast to appear. The most popular import level is "Item". 
  • Forgetting to check the "Ignore Headers" checkbox if your file has headers - This will cause your file upload/parse to fail, so if you have headers (which is common), make sure to check this checkbox)
  • Having trailing spaces in your CSV file - If you have trailing spaces on some of your values, such as part numbers, this can cause StockIQ not to be able to match the required value.
  • Excel truncating leading zeroes off of your part numbers - ensure your source data is set that the column for the part numbers is text-type, and non-numeric.

Related articles