- Getting Started
- Dynamic Planning
- Dynamic Planning Overview
- Write Back
- Aggregation Process
- User Security
- Direct Access to PCR Model
- Frequently Asked Questions
- Download & Installation
- SpotlightXL
- Analyze
- Report
- Excel Report
- Spotlight for Office
- Model
- Manage
- Token Management
- Write Back
- Dynamic Planning How to Load an External Data Source into a Master Model (Legacy)
- Spotlight Web
- Spotlight for Google Sheets
- Breakback
- Dynamic Planning API Library
- Base URL
- List of APIs
- Append Attributes (Master and Analytic Models)
- Sample Payload for Creating or Appending Attributes and Specifying the Member-Attribute Mapping
- Sample Payload for Updating the Member-Attribute Mapping for Previously Saved Attributes
- Response
- Parameters
- Sample Payload for Creating or Appending Attributes (Without Display Labels, Without Specifying the Member-Attribute Mapping)
- Sample Payload for Creating or Appending Attributes With Display Labels (Without Specifying the Member-Attribute Mapping)
- Append Dimension Members (Master and Analytic Models)
- Calculation Run (Master and Analytic Models)
- Calculation Status (Master and Analytic Models)
- Clear Data (Master and Analytic Models)
- Clear Leaf Data (Master and Analytic Models)
- Export Dimension / Attribute Hierarchy (Master and Analytic Models)
- Export Leaf-Level Data (Scoped) (Master and Analytic Models)
- Load Data (Master and Analytic Models)
- Clear Data (External Source Models)
- Clear Dimension Members (Metadata) (External Source Models)
- Export Leaf-Level Data (Scoped) (External Source Models)
- Load Data (External Source Models)
- Load Dimension Members (Metadata) (External Source Models)
- Create Users and Groups (User Management)
- List Users and Groups (User Management)
- Dynamic Planning: Group Access API
- How-Tos
- Dynamic Planning: How to Create a Report Based Off Multiple Models
- Dynamic Planning: How to Create a Report Based Off an Existing Report
- Dynamic Planning: How to Configure the Number of Invalid Login Attempts
- How to Open a Second Instance of SpotlightXL
- Dynamic Planning: How to Update a Dimension Hierarchy Without Clearing a Model
- Dynamic Planning How to Use Variables in Views and Reports
- Dynamic Planning How to Use the Metadata Viewer
- Dynamic Planning How to Use Excel Based Formulas
- Dynamic Planning How to Use Drag, Drop, Pivot, and Zoom Actions to Analyze Data
- Dynamic Planning How to Use Currency Exchange Rates
- Dynamic Planning How to Unlock a Locked User
- Dynamic Planning How to Use Change Data Tracking
- Dynamic Planning How to Specify Match Criteria
- Dynamic Planning How to Share a POV Dimension Across Models in a Report
- Dynamic Planning How to Set up a Power User with Access to Everything
- Dynamic Planning How to Select Data and Member Design Manager Options to Populate Report Values
- Dynamic Planning How to Save Data on Attributes for Views and Reports
- Dynamic Planning How to Restore a Model
- Dynamic Planning How to Perform Multi-Tab Analysis
- Dynamic Planning How to Lock Members in a View or Report to Prevent Modification
- Dynamic Planning How to Lock and Unlock Scaling
- Dynamic Planning How to Lock a Model to Prevent Modification or Clearing
- Dynamic Planning How to Lock a User and Prevent Sign On
- Dynamic Planning How to Generate a Model
- Dynamic Planning How to Filter Members Using Design Manager
- Dynamic Planning How to Download Data from the Structured Planning, Consolidation, and Reporting Applications into Dynamic Planning
- Dynamic Planning How to Delete a Model
- Dynamic Planning How to Define Multiple Number Formats for Dimension Members in Views
- Dynamic Planning How to Define Dimension Security
- Dynamic Planning How to Create Dimensions as Attributes
- Dynamic Planning How to Create and Load External Source Models
- Dynamic Planning How to Create a View and Specify Properties
- Dynamic Planning How to Create a Report off a Model and a View
- Dynamic Planning How to Create a Map
- Dynamic Planning: How to Clear a Model
- Dynamic Planning How to Cascade a Report
- Dynamic Planning How to Apply Scope to a Model
- Dynamic Planning How to Add or Delete Dimensions from a Model
- Dynamic Planning How to Add a Leaf Level Member to a Hierarchy
- Dynamic Planning How to Add a Chart to a Report
- Dynamic Planning How to Work with Model, View, and Report Folder and Organization Functionality
- Dynamic Planning How to Lock Dimension Members
- Dynamic Planning How to Find Data Cells Quickly
- Dynamic Planning How to Define Substitution Variables
- Dynamic Planning How to Backup a Model
- Dynamic Planning How to Add a Group and User Group
- Refrences
- Ref1
- Dynamic Planning Publishing and Opening Excel Reports, Word Reports, and PowerPoint Reports
- Purpose of Breakback
- Breakback Types
- Example Substitution Variable Expressions
- Prerequisites
- Install Planful Spotlight Add-on for Google Sheets
- Log in to Planful Spotlight Add-on
- Single Sign-On (SSO) for Planful Spotlight on Google Sheets
- Reports
- Using Breakback Summary
- Breakback and Data Locking
- Limitations
- Running Breakback from a Calculation
- Account Details
- Dynamic Planning User Groups
- Dynamic Planning Model Validation
- Dynamic Planning: Calculation Scheduler Option in Spotlight Web
- Spotlight User Summary
- Dynamic Planning Spotlight User Guide Supported Chart Types and Formatting on the Web
- Dynamic Planning Spotlight User Guide Report Task
- Dynamic Planning Spotlight User Guide Model Task
- Dynamic Planning Spotlight User Guide Analyze Task
- Creating and Formatting a Chart in PowerPoint or Word with Spotlight for Office
- Copying Spotlight Formulas from an Excel View or Report to PowerPoint
- Username and Password Security
- To launch Spotlight independently from a web browser:
- To launch Spotlight from the Planful application
- Supported Browsers
- Model
- Report
- Analyze
- Overview
- Dynamic Planning: Group Access
- About Dynamic Planning
- Spotlight versus SpotlightXL
- User Roles
- User Role Permission, Access, and Security
- User Types
- Unified Tenant
- Quick Summary of How to Assign or Restrict Access
- User Roles, Access, and Security
- Introduction to SpotlightXL
- Username and Password Security
- Accessing SpotlightXL
- Manage
- Model
- Report
- Analyze
- Overview
- Dynamic Planning Analyze Task
- SpotlightXL User Summary
- Dynamic Planning: SpotlightXL and Spotlight for Office Installation
- Using Dynamic Commentary in Excel Reports
- Dynamic Planning Global Settings for View and Report Properties
- Dynamic Planning SpotlightXL User Guide Function Support
- Dynamic Planning View and Report Permissions
- Dynamic Planning Using Substitution Variables and Expressions with Excel Reports
- Dynamic Planning SpotlightXL User Guide Chart Formatting and Types
- Dynamic Planning Sharing Excel Reports with People without a Dynamic Planning License Using Snapshots
- Dynamic Planning Setting Up Excel Reporting Security
- Dynamic Planning Setting Up Excel Reporting
- Dynamic Planning Setting Up an Assumptions Worksheet
- Dynamic Planning Report Design Using Substitution Variables and Expressions
- Dynamic Planning Repeat Headers Across Multiple Pages
- Dynamic Planning Refreshing and Formatting Excel Reports
- Dynamic Planning Overview of Excel Reporting
- Dynamic Planning Negative Amounts in Reports
- Dynamic Planning Navigation Access
- Dynamic Planning Multi-Tab Reporting
- Dynamic Planning Model Task in Excel
- Dynamic Planning Report Task
- Dynamic Planning Cascade Reporting
- Dynamic Planning Application Administration
- Creating Formulas in the External Source Model using Model Manager
- Creating Fields in the External Source Model using Model Manager
- Creating an External Source Model using Model Manager
- External Source Model Process
- List of Key External Source Model Features
- External Source Model Overview
- External Source Model Benefits
- Guard Rails
- Model Manager in Spotlight Honors User Group Security for all ESM Artifacts
- Drill Through in Views Accessed Via SpotlightXL or Spotlight Honor Group Access for ESMs
- Calculations in SpotlightXL Honor Group Access for ESMs
- Group Access to ESM Views
- Model Permissions for ESM Models
- Managing Group Access
- External Source Model DLR- Calculations tab Introduced
- Arabic and Chinese character support for ESM Data Load
- Examples of Include in Clear Data Loads
- Example of Creating a Data Load Rule for an External Source Model
- Using External Source Models in the Data Load Rule Definition Screens
- Best Practice Recommendations
- Details and Limitations
- Quick Summary
- Loading Data into the External Source Model
- External Source Model Field Types, Expressions, and Formulas
- Dynamic Planning External Source Model
- Dynamic Planning Export Data
- Transferring Data from One External Source Model to Another External Source Model
- Transferring Data from Analytical Model to ESM Model
- External Source Model Best Practices
- Backup and Restore of External Source Models
- Using Views to Input Future Opportunities into the Source Model
- Viewing the Data in the Master Model
- Using a Calculation to Load Data into the Master Model
- Defining a Map from Source Model to Master Model
- Creating a Master Model
- Loading Data into the Opportunity Data External Source Model
- Explanation of the DaysFactor Function
- Explanation of the Lookup Function
- Defining the External Source Model
- Viewing the Source Data
- Online Help, Support, Community, and Solution Hub
- Supported Browsers for SpotlightXL and Spotlight
- Automatic Signout
- Unique URLs for Artifacts
- Overview
- Where to Download SpotlightXL and Version Information
- What Version of SpotlightXL Am I Using?
- Incompatible SpotlightXL Error
- System Requirements
- Installing SpotlightXL and Spotlight for Office
- How to Use the ClickOnce Installer for Automatic Upgrades
- How to Use the InstallShield MSI Installer
- Upgrading SpotlightXL and Spotlight for Office
- Troubleshooting Tips
- ClickOnce Flexible Deployment
- Installation Best Practices
- User Roles
- View Formula in External Source Model
- Loading Data into External Source Models using PCR Data Load Rules
- Access to ESMs
- Step-by-Step Use Case for Forecasting Revenue Spread Over Time
- Dynamic Planning Data Locking
- Dynamic Planning Model Substitution Variables
- Dynamic Planning Model Lookup
- Dynamic Planning Groups
- Accessing Spotlight
- Dynamic Planning: Spotlight for Office User Guide
- Dynamic Planning: Admin Guides
- Dynamic Planning: Integration Admin Guide
- Overview
- Definitions
- Model Design - Connecting to the Structured Planning, Consolidation, and Reporting Applications
- Model Design - Setting up the Source Model in Dynamic Planning
- Model Design - Validation Report for Duplicate Members
- Model Design - Setting up the Target/Master Model using a Map
- Model Design - Target Model Design
- Populating the Source Model - Data Download
- Clearing the Source Model
- Populating the Target Model
- Viewing the Default Calculation
- Model Statistics
- Maintaining and Refreshing the Model
- Dynamic Planning: New Improved Aggregation Process
- Using Drill-Through
- Views and Reports
- Ability to Automatically Add Members When Writing Data Back From Dynamic Planning to Structured Planning, Consolidation, and Reporting
- Integration Admin Options
- Dynamic Planning: User Setup and Security Guide
- Overview
- Assign User Group Access to Models, Reports, and Views
- Quick Summary of How to Assign or Restrict Access
- Set up a Contributor User with Access to Specific Models
- Definition of Security Levels
- Set up a Reviewer User with Access to Specific Models, Views, Reports, and Dimension Members
- Set up a Power User with Access to Everything
- Dynamic Planning: Model Manager, Setup in Planful Applications
- Dynamic Planning: Best Practices for Model Building, Model Refresh, and Model Restructure
- Dynamic Planning: Integration Admin Guide
- Dynamic Planning: Subject Matter Guides
- Report Collection for Dynamic Planning
- Dynamic Planning: Best Practices for Model Building, Model Refresh, and Model Restructure
- ESM (External Source Models) Canvas
- Analyze
- Dynamic Planning: Using Direct Access (aka Direct Connect) to PCR Financial Reporting
- Key Benefits of Direct Access Models
- Setting Up the Connection to PCR
- Creating Users
- Creating a Direct Access to PCR Model
- Metadata Dimension Security for the Scenario Dimension
- Support for PCR Attributes, Attribute Hierarchies, and Alternate Hierarchies
- Adding Attributes, Attribute Hierarchies, or Alternate Hierarchies to Existing Direct Access Models
- Creating a Direct Access Model with Attributes, Attribute Hierarchies, or Alternate Hierarchies
- Viewing Attributes, Attribute Hierarchies, or Alternate Hierarchies in Spotlight Model Manager
- Viewing Attributes, Attribute Hierarchies, or Alternate Hierarchies in SpotlightXL Model Setup
- Support for _Attribute_Default in Direct Access to PCR Models
- Support for PCR Substitution Variables and Derived Variables
- Viewing PCR Substitution Variables and Derived Variables in SpotlightXL
- Using PCR Substitution Variables and Derived Variables in Reports at Runtime
- Adding Substitution Variables and Derived Variables to Existing Direct Access Models
- Creating a Direct Access Model with PCR Substitution Variables and Derived Variables
- Adding PCR Substitution Variables, Derived Variables, and Expressions to Reports
- Reporting and Analysis Primer
- Multiple POV Selections in SpotlightXL and Spotlight for Direct Connect Models
- Reporting Dimension Custom Labels Displayed in Direct Connect Models
- Support for PCR Cube Default Members for System-Defined Dimensions in New Views, Formatted Reports, and Excel Reports
- Support for Drill Through in Direct Connect Models in Both SpotlightXL and Spotlight
- Maintenance Options
- Information for Experienced Power and Contributor Users
- Converting a Master Model into a Direct Access to PCR Model
- Dynamic Planning Converting a Master Model into a Direct Access to PCR Model
- Using Direct Access (aka Direct Connect) to PCR Financial Reporting
- Dynamic Planning: Predict Signals
- Dynamic Planning: How To Tasks
- Dynamic Planning How to Add a User
- Dynamic Planning Manage Task
- Ref1
- Structured Planning
- Tasks to Configure for the Planning Module
- Planning Control Panel Intro
- Template Input and View Modes
- Entity Workflow
- Dynamic Commentary in Planning Template
- Working with Reports
- Workforce Planning - Classic UI
- MyPlan Guides
- MyPlan User Guide
- Accessing MyPlan
- Dissecting the MyPlan Interface
- Retained In-Memory Actions
- How Actions Are Calculated
- Adjusting, Updating, Adding, and Editing Accounts in MyPlan
- How to Add New Headcount
- How to Edit a Headcount
- How to Add Multiple Employees at Once
- How to Edit Multiple New Hires at Once
- How to Add a Headcount Based on an Existing Headcount and Edit Details
- How to Add an Asset
- How to Delete a Headcount
- How to Add an Asset Not Yet Budgeted
- How to Add An Asset for a Budget Entity When the Asset Definition Does Not Exist
- How to Edit an Asset
- How to Update an Account
- Working with Templates in MyPlan
- Activity Manager
- How to Submit a Budget/Forecast
- Template Locking
- Limitations
- MyPlan User Options
- MyPlan Admin Guide
- MyPlan User Guide
- Capital Planning
- Initiative Planning
- Workforce Planning
- References
- Structured Planning Admin Guide
- Configuration
- Security Administration
- Currency
- Hierarchies and Entities
- Overview
- Why Hierarchies Are Needed
- Types of Hierarchies You Can Create
- Accessing the Hierarchy Management Interface
- Working in the Hierarchy Management Interface
- Setup Entity Type Hierarchies
- Financial Hierarchy Overview
- Setup Finance Hierarchies
- Understanding Calculated Members
- Calculated Member Example - Creating a Member and Including it in a Dynamic Report
- Load Data to Planful
- Scenario Setup
- Overview
- Description of Fields on the Scenario List Page
- Performing Scenario / Template Maintenance
- Scenario Types
- Creating a Budget Scenario
- Creating a Forecast Scenario
- Creating a Plan Scenario
- Creating a Preloaded Budget Scenario
- How to Delete a Scenario
- Global Fields
- Refreshing Actuals (Closed Period Data)
- Mapping Templates to Scenarios
- Securing Scenarios
- Why Secure Scenarios
- When to Secure Scenarios
- How to Secure Scenarios
- How to Provide a User with Edit and View Access to Scenarios
- How to Automatically Have Access to all Future Scenarios You Create
- How to Provide Scenario Access to Multiple Users at Once
- How to Set Up Scenario Security for Reporting Purposes
- Clear Data
- Additional Scenario Topics
- Compare Scenarios
- Intelligent Data Seeding with the Plan Scenario
- Data Seeding Without the Plan Scenario
- Reporting Area Admin
- Dimension Security
- Standard Reports
- Planning Templates
- What are Templates and Why are They Needed
- Template Types
- Template List Application Page
- Time Sets - Everything You Need to Know
- How to Add a Block Template Type
- How to Add a Global Template - Single Copy Template Type
- How to Add a Global Template - Entity Copy Template Type
- How to Add an Allocation Template Type
- How to Add a Capital Template Type
- How to Add an Workforce Planning Template Type
- How to Add an HR Template Type
- How to Add a Line Item Template Type
- How to Add an Initiative Template
- Template Setup
- Understanding Template Setup
- Understanding the Template Setup Application Page
- Template Line Types and Column Types
- Understanding Line Item Template Setup
- Understanding Capital Template Setup
- Understanding Initiative Template Setup
- Template Start and End Dates
- Avoiding Line Discrepancies Between Sub and Main Templates
- Reference Cube
- Setting Up and Applying Global Fields to Templates (Optional)
- Apply Global Fields to Template Lines Accessed from the Planning Control Panel
- What are Global Fields and Why are They Used
- Add a Global Field
- Map the Global Field to Another Scenario and Budget Entities
- Mass Upload Global Fields
- Map Global Fields to Templates for Budget Entity and Scenario
- Global Fields in the Planning Control Panel
- Input Global Field Data
- Setting Up and Using Spreads in Templates (Optional)
- Attributes
- Template Setup Examples
- What To Do Once Template Setup is Complete
- Simulation Engine
- User Management
- Loading Users to Planful
- Security Cheat Sheet
- User Groups
- Navigation Role and Access
- Two Step Verification
- Web Service Access
- SSO Users
- Locking Users Out of Planful
- Copying Users
- Approval Roles
- Approval Roles - What Are They and How Are They Used
- Example of a Typical Approval Role Configuration
- How to Add and Edit Approval Roles
- How to Bulk Export Approval Roles and Budget Entity Permissions for a Single User/All Users
- How to Bulk Import Approval Roles
- Understanding Operating Budget Template Actions for Approval Roles
- Understanding Initiative Budgeting Approval Actions
- Scenario Access
- Security for Add-Ins
- Data Integration Security
- Cloud Scheduler
- What is Cloud Scheduler and How Does It Benefit Me?
- Providing Users with Access to Cloud Scheduler
- Understanding Process Flow and Job Manager
- Description of Fields on the Process Flow Page
- How to Add A Process Flow
- Auditing Process Flows
- Overriding Substitution Variables for Report Collections and Financial Package When Running a Process Flow
- Description of Fields on the Job Manager Page
- Initiative Workflow
- Adding Assets to Initiatives
- Add an Initiative to the Initiative Template and Start Budgeting for that Initiative
- Open the Template in the Planning Control Panel
- Map the Initiative Template to a Scenario(s)
- Map Attributes to Initiatives
- Add Attributes
- Set up Attributes to Map to Initiatives
- Setup/Design the Initiative Template
- Set Up Categories
- References
- Workforce Planning Admin Guide
- Workforce Planning
- Define Compensation Items for Workforce Planning
- Description of Fields on the Compensation Item Page
- Compensation Basis Examples
- Add Compensation Items Overview
- Description of Fields on the Add Compensation Item Page
- How to Define Compensation Groups for Workforce Planning
- How to Define Workforce Attributes for Workforce Planning
- How to Define Employee Types for Workforce Planning
- Overview of Pay Plans - Workforce Planning
- How to Set Up Pay Plans - Workforce Planning
- What is Pays Setup and What is the Purpose of It
- How to Complete Pays Setup - Workforce Planning?
- Employees Application Page - Workforce Planning
- Personalize the Employee list page - Workforce Planning
- Description of Fields on Employee Add Page
- Automated Employee Processing
- How to Extract Allocated Employee Information
- Security Administration
- Workforce Defaults
- Add Employees & Details
- Templates
- Template Mapping
- User Access
- Reporting
- Employee Type
- Advance Search in Workforce Planning
- Workforce Planning
- Workforce Planning - Enhanced UI
- How-Tos
- Subject Matter Guides
- Workforce Planning Admin Guide
- Set Up Approval Roles for the New Template
- Add an Initiative Template
- Overview of Steps to Set Up Initiative Planning
- Inputting Data via the Planning Control Panel
- User Security
- Scenario and Entity Mapping
- Adding Capital Templates
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- Adding Finance Modes
- Adding Assets
- Adding Asset Categories
- Configuring Time Sets
- Configuring Capital Planning Attributes
- Configuring Time Sets
- Reporting
- Structured Planning User Guide
- Snapshot introduced in Planning Control Panel
- Performing Template Input
- Template View Mode
- Template Notifications
- View User History of Any Template
- Global Templates
- Block Templates
- Workforce Planning Templates
- Capital Templates
- Initiative Templates
- Line Item Template
- Allocation Template
- Approvers
- Workforce Planning UI
- Snapshot Restore Guidelines
- Structured Planning Admin Guide
- Consolidation
- Prerequisites
- Consolidation Control Panel
- How-tos?
- How to Configure Retained Earnings Roll Forward?
- How to Setup for Enhanced Retained Earnings Roll Forward?
- How to Close with Consolidation?
- How to Process Consolidation With Configurable Steps?
- How to Unlock Multiple Periods in Consolidation Process?
- How to Lock Multiple Periods in Consolidation Process?
- How to Provide Security for Centralized Consolidation Users?
- How to Provide Security for Decentralized Consolidation Users?
- How to Disable Display of Security Option from the Consolidation Control Panel?
- References
- Consolidation Accounts Setup Explained
- Intro to Consolidation
- Standard Journals
- Reporting
- Recurring Journals
- Dynamic Journals
- Non Controlling Interest
- Reclassifications
- Eliminations
- Validations
- Performing Entry Actions on Multiple Entries
- Consolidation Security
- Interim Currency
- How to Add Members to Sibling Hierarchies
- How to Create a Sibling Hierarchy Using a Sibling Hierarchy as the Source
- How to Create a Sibling Hierarchy Using the Main Company Hierarchy as the Source
- Source Hierarchies Used to Create Sibling Hierarchies
- What are Sibling Hierarchies and Why Do I Need to Create Them
- How to Provide Users with Access to Hierarchies
- What is the Hierarchy Usage Report
- Disallowed Strings for Attributes and Dimensions
- Dimension Locking
- Auditing Hierarchies
- Entity Currency
- Security and Accounts
- Configuration Tasks
- When Consolidation Is Needed
- Security Administration
- User Management
- Non Controlling Interest
- Centralized Security
- Cloud Scheduler for Consolidation Admin
- Set Up Currency
- Difference Between Common Currency, Local Currency, and Interim Currency
- Currency Use Case - Common Currency vs. Local Currency
- How to Set Up Currency and Currency Type
- What are Currency and Currency Type Exceptions and When Do I Need Them
- How to Delist Currency Types
- Currency Exchange Rate
- Hyperinflationary Economy
- Calculation Exceptions
- CTA
- Reporting Currency
- Entity Currency
- Load Data to Planful
- What Are Currency Exceptions and Do I Need to Set Them Up
- How to Setup and View Currency Exceptions
- Consolidation - Income Statement Retained Earnings Account & Retained Earnings Roll Forward
- Journals
- Reclassifications
- Standard Reports
- Security Overview
- Eliminations
- Validation
- Setting Up Hierarchies
- Report Categories
- Hierarchy - Reporting, Entity, & Interim Currency
- What is Org by Period, How to Access Org by Period, and How to Configure It
- How to Create an Alternate Hierarchy
- Dated Hierarchies FAQs
- Org By Period Report Generation
- How to Add a Dated Hierarchy
- How to Create an Alternate Hierarchy Using the Copy Functionality
- Calculated Member Example - Creating a Member and Including it in a Dynamic Report
- What are Calculated Members and Why Do I Need to Create Them
- Setup for Finance Type Hierarchies
- Overview
- What are Reporting Members and Why Do I Need to Create Them
- Why Hierarchies Are Needed
- Types of Hierarchies Explained
- Accessing the Hierarchy Management Interface
- Exploring the Hierarchy Management Interface Panes
- Exploring the Top Ribbon
- Exploring the Right Pane - Member Properties
- Exploring the Right Pane - Default Properties
- Exploring the Right Pane - Segment Properties
- Exploring the Right Pane - Interim Currencies
- Exploring the Right Pane - User-Defined Attributes
- Setting Up Hierarchies Summary
- Scenario Setup
- Overview
- Description of Fields on the Scenario List Page
- How to Provide Scenario Access to Multiple Users at Once
- Performing Scenario / Template Maintenance
- Why do I Need to Lock a Scenario
- How to Lock a Scenario
- How to Unlock a Scenario
- Scenario Types
- How, Why and When to Refresh Actuals (Closed Period Data)
- How, Why, and When to Refresh Preloaded Data for Preloaded Scenarios
- How, Why, and When to Secure Scenarios
- How to Provide a User with Edit and View Access to Scenarios
- How to Automatically Have Access to all Future Scenarios You Create
- How to Set Up Scenario Security for Reporting Purposes
- How to Create a Scenario Based on Two Existing Scenarios
- Data Seeding
- Processing Consolidations - What Happens During the Consolidation Process
- Override Workflow Actions for the Locking Period
- Review Consolidation Setup
- Consolidation Process Summary
- Copying Journal Entry Data
- What Is Validation Functionality and How to Enable It for Users
- How to Delete a Template from a Locked Scenario
- How to Add a Report Category
- What are Alternate Hierarchies and How are They Used
- What are Dated Hierarchies and How are They Used
- How to Add a Calculated Member to an Account Dimension
- How to Use Cloud Processing for Consolidation Hierarchies
- What is Two Step Verification, How Does It Work, and How Do I Enable It
- How to Add a User Group
- What Is Elimination Functionality
- How to Set Up an Elimination Company
- Reports
- Reports Prerequisites
- Reports Configurations
- Reports Home Page
- Report Folder
- Report Document
- Report Sets
- Report Collection
- Financial Package
- Dynamic Reports
- Working with Dynamic Reports
- Build and Run a Dynamic Report
- Example of Building a Dynamic Report
- Enhanced Drill Through in Dynamic Reports
- Template Notes in Dynamic Reports
- Dynamic Commentary
- Currency Display in Dynamic Reports
- Automatic Data Refresh
- Print Dynamic Report
- Exporting Dynamic Reports Output in Unprotected View
- View History from Reports
- Dynamic Reports Use Case
- Best Practices
- Dynamic Report Limitations
- Workforce Reporting
- Reports - References
- Dynamic Reports
- Overview
- Composition of a Dynamic Report
- Modify the Look and Feel of Dynamic Report Output
- How to Access the Format Pane
- An Explanation of Standard Formatting Functionality
- Cell Level Formatting Explained
- How to Reset Formatting Selections
- Situations When Formatting is Disabled or Can't be Applied
- How to Resize and Save Groups of Columns in a Dynamic Report
- Template Notes in Dynamic Reports
- How to Post a Comment
- Dynamic Reports Mapped to Planning Templates
- Dynamic Reports Mapped to Planning Templates Overview
- Copying Dynamic Reports
- How to Create and Modify Dynamic Reports
- Setting Up and Using Substitution Variables
- Limitations
- Overview
- How to Set Up Substitution Variables
- How to Use Substitution Variables in Dynamic Reports
- Dynamic Reports Options
- Best Practices and Tips
- References - Report Set
- Report Collection
- Financial Package
- Build and Run a Financial Package
- Reports User Guide
- File Cabinet
- Spotlight for Office
- Overview
- How to Log Into Spotlight for Office
- Copying Spotlight Formulas from an Excel View or Report to PowerPoint
- Copying Spotlight Formulas from an Excel View or Report to Word
- Copying Spotlight Formulas to PowerPoint using Copy Formulas
- Copying Spotlight Formulas to PowerPoint using Design PowerPoint Report
- Using Spotlight for Office
- Reports Administration Guide
- Dynamic Reports
- Dashboards
- Dashboards User Guide
- What are Dashboards
- Why Use Dashboards
- When to Use Dashboards
- How to Use Dashboards
- Dashboard Designer
- Using Consider Absolute Value Denominator Option for KPI Chart
- Using Dynamic Planning Data in a Dashboard
- Dynamic Report Chart
- Dashboard Read
- Saving Dashboards
- Refreshing Dashboards
- Using Filters
- Custom Color Schemes
- How to Build a Workforce Dashboard
- Setting Dashboards as Your Homepage
- View History from Dashboards
- Best Practices
- Dashboards User Options
- Dashboards Administration Guide
- Providing Users with Navigation Access to Dashboards
- Dashboards Reporting Area
- Use Dynamic Planning Data in Dashboards
- Dashboards Dimension Security
- Dashboards Scenario Security
- Dashboards Budget Entity Security
- Substitution Variables
- Pre-Defined Custom Members/Formulas
- Dashboard Sharing
- Edit Versus View Access to Dashboards
- Dashboards Versus Dynamic Reports
- Dashboard Updates
- Recommended Dashboards Screen Resolution
- Auditing Dashboards
- Best Practices
- Dashboards Administration
- Dashboards User Guide
- Planful AI
- Predict
- Budget Manager Experience
- Set Up Budget Managers
- Getting Started with Budget Manager Experience
- Sharing Templates with Budget Managers
- Manage Account Settings
- Track the Shared Templates
- Share Reports Via Link
- Predict in Budget Manager Experience
- Dashboards in Budget Manager Experience
- Workforce Planning Templates in Budget Manager Experience
- Maintenance
- Administration
- User & Role Management
- Scenario Setup
- Security Administration
- Configuration Tasks
- Workdays Setup
- Cloud Scheduler
- Customize Branding
- MyPlan Configuration
- Comments Manager
- Multi-Currency
- Hierarchy Management Overview
- Hierarchy Management Interface Overview
- Creating and Managing Additional Finance Hierarchies
- Adding Report Categories
- How-Tos
- Setting Up Attributes for Attribute Hierarchies
- Understanding Best Practices - Finance Hierarchies
- Understanding Reporting Members and Calculated Members
- Setting Up an Attribute Hierarchy
- Configuring OLAP Cube
- Understanding Example of How to Use Attributes
- Adding Another Level to Your Attribute Hierarchy
- Reflecting New Attribute Levels in Reports
- Setting Up An Attribute Used in Reporting and Enabling Drill Down to Dimension Member
- Mapping and Unmapping Workforce Planning Default Measures for Reporting Purposes
- Understanding Best Practices - Attribute Hierarchies
- Setting Up an Alternate Hierarchy
- Setting Up a Dated Hierarchy
- Entity Hierarchy Overview
- Sibling Hierarchies - Multiple Set of Books Overview
- Versioned Hierarchy Management in Dynamic Planning
- Exploring Additional Hierarchy Topics
- How-Tos
- Exploring the Frequently Asked Questions (FAQs) for Hierarchies
- FAQs for Hierarchies
- Planning Templates
- Global Fields
- Attributes
- Line Item Category
- Template Setup
- Template List
- Add Template
- Attribute Columns
- Deleting Attribute Columns
- Avoiding Mismatched Lines Between Sub and Main Templates
- Initiative Template Setup
- Time Sets
- Template Types
- How to Add a Global Template - Single Copy Template Type?
- How to Add a Global Template- Entity Copy?
- How to Add a Block Template Type?
- How to Add HR - Workforce Planning Template?
- How to Add an HR Template Type?
- How to Add an Allocation Template Type?
- How to Add a Capital Template Type?
- How to Add a Line Item Template Type?
- How to Add an Initiative Template?
- Template Examples
- Add Template
- Template Setup
- Mappings
- Spreads Set Up
- What To Do Once Template Setup is Complete?
- Template List
- Additional Template Information
- References
- What Are Time Sets?
- How to Configure Time Sets
- How to Create an Approval Role and Provide Users with Approval Role Access
- How to Provide Users with Scenario Access
- Attributes
- Applying Spreads to a Template Accessed via the Planning Control Panel
- Rules for Template RC Lines
- What are Spreads and How are They Used
- Initiative Template Setup
- Capital Template Setup
- How to Set Up a Line Item Template
- How to Set Up Line Item Categories
- How to Define a Time Set
- How to Set the Default Time Set
- Understanding the Template Setup Application Page
- What is Template Setup
- Template Column Types
- How to Map a Template to a Scenario
- How to Map a Template to an Entity
- What are Templates and Why are They Needed
- Template Types
- Description of Fields on the Template List Page
- Line Item Template Setup
- Spreads
- Template Setup Examples
- How to Access the Template List Page
- Opt In Features
- Publishing Templates
- Workforce
- Workforce Planning Setup
- Employees
- Adding Employees
- Add a Profile Picture to Employee Profile
- Reports Option for Employees
- Mass Update
- Using Customize Roster to Add Compensation Items
- Changing Budget Raise Percentage
- Employee Positions Outside of the Current Budget Cycle
- Automated Employee Processing
- Performance Statistics - Workforce Planning Employee Page
- Calculating Budget Review Date and Current Review Date
- Pop-up Message Introduced across all the windows in Workforce Planning Setup
- Create a Duplicate Employee
- Copy Employees
- Use Cases on How to Update Several Employees at Once
- How-Tos
- Compensation Groups
- Compensation Items
- Compensation Basis
- Marginal Benefit Calculation Setup Examples
- Situations When You Can't Edit a Compensation Item
- Understanding Pre-built Calculation Basis
- Linking to Global Fields
- How-Tos
- How to View Compensation Items in a Workforce Planning Budget Report?
- How to Set up 401k Match?
- How to Copy all Compensation Items and Rates from One Scenario to Another?
- How to Input Data Using the Direct Data Input Compensation Item?
- How to Lock Compensation Items to Prevent Modification?
- How Changes to Compensation Items Affect Running Scenarios?
- How to Update Compensation Items Loaded via Data Load Rules?
- How to Make the Bonus Expense Calculation Variable?
- How to Select Compensation Items for Calculating Wages?
- Employee Types
- Pay Plans
- Workforce Attributes
- User Access
- Employees
- Export Employee Data
- References
- Workforce Planning Admin Guide
- Configuring Workforce Planning
- Security Administration
- Workforce Defaults
- Add Employees & Details
- Templates
- Template Mapping
- User Access
- Reporting
- Workforce Planning Admin Options
- Understanding Tiers on Employee Attribute - Value in Hours per Period with Annual Cap
- Understanding Tiers on Employee Attribute - Value in Hours per Period
- Understanding Tiers on Employee Attribute - Value in Wages with Varying Annual Cap
- Understanding Rate x Hour
- Workforce Planning Admin Guide
- Workforce Planning Setup
- Capital Planning
- References
- Capital Planning Admin Guide
- Reporting
- Adding Assets
- Adding Finance Modes
- Adding Capital Templates
- Setting Up Finance Mode Templates and Mapping Capital Budget Lines to Your General Ledger
- User Security
- Configuring Time Sets
- Scenario and Entity Mapping
- Adding Asset Categories
- Configuring Capital Planning Attributes
- Inputting Data via the Planning Control Panel
- Capital Planning Admin Guide
- References
- Rules
- Data Integration
- Actual Data Templates
- Translations Setup
- Export Data
- Transaction Details
- Data Load Rules
- Integration Services
- Clear Data
- Data Calculation Exceptions
- Additional Topics
- API Library
- Introduction
- Data Import
- Data Export
- Structured Planning REST APIs
- Workiva Integration
- Objects Overview
- API Compliance
- Client Application Platforms
- API Access Keys
- API Calls - Overview
- Core API Calls
- Utility Calls
- Objects
- API Methods
- Clear_Data API
- Load_Data API
- Transfer_Data API
- Segment APIs
- GLData_Retrieve API
- Login API
- GetSessionId API
- GetVersion API
- GetAllCurrencies API
- GetAllCurrencyTypes API
- GetAllSegments API
- Logout API
- Employee APIs
- Approval Role Setup APIs
- Scenario Access APIs
- Dimension Member Name Update API
- Bulk User and User Group Extract API
- Dimension Security APIs
- Dimension Security Update API
- MapEntity API
- Refresh Preloaded Data API
- Best Practices and Tips
- Enumerations
- Validate Source Segment API
- Transaction Data Extract APIs
- Planful API Library
- Source Segment API
- Report Administration
- Application Audit
- Application Audit Details - Audit Area
- References
- User & Role Management
- How to Access the User and Role Management Application Page
- Navigation Role and Access
- Manually Add a User to Planful
- Exporting Navigation Role Summary and Detailed Reports
- Locking Users and Preventing Sign On to Planful
- Copy An Existing User
- Approval Roles
- Consolidation Security
- User Groups
- Scenario Access
- Add-In Security (Optional)
- Data Integration Security
- Dimension Security Configuration
- Workforce Reporting Security
- Understanding the User Management Interface - Approval Role
- Understanding the User Management Interface - User Add Page
- Understanding the User Management Interface
- Understanding the User Management Interface - Navigation Role
- Purpose of User and Role Management
- Scenario Setup
- Security Administration
- Configuration Tasks
- Workdays Setup
- Auditing the Application
- Rules - References
- Simulation Engine Guide
- What is Simulation Engine?
- When to Use Simulation Engine
- Why Simulation Engine is Needed
- How to Create a Simulation Engine Process
- How to Add Template - Entity Combinations to a Simulation Engine Process
- How to Schedule a Simulation Engine Process via Cloud Scheduler
- Copying Simulation Engine Processes From the Default Scenario to Other Scenarios
- Additional Features Available for Simulation Engine
- Simulation Engine Guide Summary
- FAQs
- Simulation Engine Guide
- Data Integration - References
- Data Load Rules
- Web Services / Boomi
- Actual Data Templates
- Integration Services
- Working with Custom Parameters for Integration Service Processes
- Adding an Integration Service Process
- Completing the Integration Services Configuration Task
- Providing Navigation Access to the Integration Services Application Page
- Editing, Deleting and Copying Integration Service Processes
- Introduction
- Cloud Services
- Transaction Details
- Google Drive
- NetSuite Integration
- FTP/SFTP Connector
- Cloud Services
- Data Integration Admin Guide
- Admin Guide to Loading Data to Planful
- Cloud Scheduler Ref.
- What is Cloud Scheduler
- How to Provide Users with Access to Cloud Scheduler
- How to Add A Process Flow
- Understanding Process Flow and Job Manager
- Description of Fields on the Process Flow Page
- Auditing Process Flows
- Overriding Substitution Variables for Report Collections and Financial Package When Running a Process Flow
- Description of Fields on the Job Manager Page
- Currency-Ref
- Difference Between Common Currency, Local Currency, and Interim Currency
- Currency Use Case - Common Currency vs. Local Currency
- Decision Hub Summary
- Interim Currency
- Currency Setup
- Currency Type Setup
- Currency Type and Currency Type Exceptions
- Mass Loading Currency Exceptions
- Loading Exchange Rates Using Data Load Rules
- Cube Settings
- Report Administration Guide
- MyPlan Admin Guide
- User & Role Management
- Administration
- Release Updates
- Release Notes
- 2025 Release Notes
- 2024 Release Notes
- 2023 Release Notes
- 2022 Release Notes
- 2021 Release Notes
- 2020 Release Notes
- 2019 Release Notes
- 2018 Release Notes
- 2017 Release Notes
- December 17 Maintenance Release
- 2.3.1 Modeling Release Notes, December
- Fall 17 Release Notes
- 2.3 Modeling Release Notes, November
- October 17 Maintenance Release
- 2.2.2 Modeling Release Notes, October
- September 17 Maintenance Release
- Summer 17 Release Notes
- 2.1.2.2 Modeling Release Notes, July/August
- July 17 Release Notes
- 2.1.2 Modeling Release Notes, July
- June 17 Release Notes
- 2.1.1 Modeling Release Notes, June
- Spring 17 Release Notes
- 2.1 Modeling Release Notes, May
- April 17 Release Notes
- 2.0.2 Modeling Release Notes, April
- March 17 Release Notes
- 2.0.1 Modeling Release Notes, March
- Winter 17 Release Notes
- 2.0 Modeling Release Notes, February
- January 17 Release Notes
- 1.9.2 Modeling Release Notes, January
- 2016 Release Notes
- Planning/Consolidation/Reporting December 16 Release Notes
- 1.9.1 Modeling Release Notes, December
- Planning/Consolidation/Reporting Fall 16 Release Notes
- 1.9 Modeling Release Notes, November
- Planning/Consolidation/Reporting October 16 Release Notes
- 1.8.2 Modeling Release Notes, October
- Planning/Consolidation/Reporting September 16 Release Notes
- 1.8.1 Modeling Release Notes, September
- Planning/Consolidation/Reporting Summer 16 Release Notes
- 1.8 Modeling Release Notes, August
- Planning/Consolidation/Reporting July 16 Maintenance Release
- 1.7.2 Modeling Release Notes, July
- Planning/Consolidation/Reporting June 16 Maintenance Release
- 1.7.1 Modeling Release Notes, June
- Planning/Consolidation/Reporting Spring 16 Feature Release
- 1.7 Modeling Release Notes, May
- Planning/Consolidation/Reporting April 16 Maintenance Release
- 1.6.2 Modeling Release Notes, April
- Planning/Consolidation/Reporting March 16 Maintenance Release
- 1.6.1 Modeling Release Notes, March
- Planning/Consolidation/Reporting Winter16 Release
- 1.6 Modeling Release Notes, February
- Planning/Consolidation/Reporting January 16 Maintenance Release
- Enhancements to Application Behavior
- 1.5.2 Modeling Release Notes, January
- Release Notes
- Additional Resources
- 24 Minutes to read
- Print
- DarkLight
- PDF
External Source Model Functions
- 24 Minutes to read
- Print
- DarkLight
- PDF
External Source Models support the use of functions within formulas for ease of calculations.
External Source Model Supported Functions
You can transform or modify data coming from the external data source using formulas. In all formulas, fields used as the arguments in the syntax must be enclosed with square brackets [ ].
The following function types are similar to those supported in Excel.
- Multiple Data Types
- Text
- Numeric
- Math
- Logical
- Date
- In PCR, the External Source Model is provided as a Load Item in Data Load Rules. When ESM is selected, all the ESMs are listed in the Load Sub-Item drop-down. These items are found in Maintenance, DLR, and Data Load Rules.
- This support applies to File, Web Services, and Copy/Paste Load Types. Existing Web Services APIs (Boomi), Clear Data, Load Data & Transfer Data are extended to External Source Models.
- Data Integration User security for users and user groups is honored for ESM data load rules. If a user or group does not have access to a DLR, they will not see it in the grid.
- ESM data loads support Comma & Dot for Thousand & Decimal separators, respectively.
- The Number of Source Columns and their mappings are automatically updated in the Data Load Rule based on the external source model selected. A maximum of 100 columns can be loaded via the Data Load Rule.
- Formula and Constant type fields are not included in the Data Load Rule because they are derived automatically during the data load.
- A maximum of 4000 characters are allowed on each field when loaded via the Data Load Rule.
- You can also use the Include in Clear Data functionality with External Source Models. You can choose to include or exclude specific fields and, based on the clear data definition, any existing data in the model is cleared before loading the updated data.
- The Data Load History is updated after the data load. Select Maintenance > Audit > Data Load History.
Functions for Multiple Data Types: Numbers, Dates, or Text Strings
- MIN(a1, a2, a3, ..., a255 ), returns the smallest or oldest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.
- See Example: Using EOMonth, EDate, Min, Max, and Text Functions
- MAX(a1, a2, a3, ..., a255 ), returns the largest or newest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.
- See Example: Using EOMonth, EDate, Min, Max, and Text Functions
- DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,...... ) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.
see Example: Using the Dropdown Function. - DROPDOWN("Model::[ESM Model Name]","[Key Field Name]" ) returns the dropdown value with the Key Field Name from an existing External Source Model name. See Example: Dropdown function with External Source Model Field referenced values.
Example: Using EOMonth, EDate, Min, Max, and Text Functions
The following External Source Model uses three input fields and five functions.
Three fields are inputted:
- Project name
- Project Start date
- Term in Months
- The other five fields are calculated:
- Project End Date uses the EDATE function to calculate the end date by adding the specified number of months to the project start date (assuming the Term is a positive number). It returns a date that is on the same day of the month as the project start date.
- Project End Month date uses the EOMONTH function to calculate the end month by adding the specified number of months to the project start date (assuming the Term is a positive number). It returns a date that is on the last day of the month as the project start date.
- MIN of Dates uses the MIN function to specify which date is earlier: Project Start or Project End.
- MAX of Dates uses the MAX function to specify which date is later: Project Start or Project End.
- Month uses the TEXT function to indicate what month the project ends, and returns the information in the format MMM-YY.
The external data source contains the following fields.
After loading this data into the External Source Model, the five formula fields are calculated.
Example: Using the Dropdown Function
The DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,.....) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.
- When you use the dropdown on the text field, the parameter is defined as text in double-quotes separated by a comma.
- When you use the dropdown on the numeric field, the parameters are defined as normal numbers, decimal numbers without double quotes and separated by a comma.
- When you use the dropdown on the date field, the parameters are defined in date format in double-quotes separated by a comma. Date field type supports six types of formats (MMM-YYYY, MMM-YY, DD/MM/YYYY, MM/DD/YYYY, DD-MM-YYYY, and MM-DD-YYYY).
Following are the limitations for using the dropdown function:
- You cannot use duplicate values for the parameters
- You cannot use more than 100 parameters
- Parameter values must be case insensitive
- You can only enter those values manually which are already present in the dropdown.
For example, let’s say you want to list the numbers of units sold per region in a month as shown below.
You can select the region from the dropdown that is defined in the source model.
You can select the units sold from the dropdown that is defined in the source model.
You can select the month from the dropdown that is defined in the source model.
Example: Dropdown function with External Source Model Field referenced values
This function returns the dropdown value with the Key Field Name from an existing External Source Model name.
For example, let’s say you have one existing External Source Model with Dropdown function, and you want to create a new External Source Model model with Dropdown function by reusing the existing External Source Model fields.
The existing External Source Model is named as DropdownSource. The following images gives you a view of DropdownSource model and the fields.
The following image display the source data of DropdownSource model.
If you want to use the DropdownSource fields in the DropdownTarget model, create the DropdownTarget model using the dropdown function with External Source Models field referenced values as shown in the below figure:
The DropdownTarget model can now use the existing DropdownSource fields and you can select the Units and Month field valued from the dropdown list as shown in the below figure:
Text Functions
- LEFT([text field ], n ), returns n characters from the left side of a text field . If n is omitted, one character is returned.
- RIGHT([text field ], n ), returns n characters from the right side of a text field . If n is omitted, one character is returned.
- CONCATENATE([text field1 ], [text field2 ], [text field3 ], ...), returns the combination of text field1 and text field2 (and optionally additional text fields) as one longer text string.
- MID([text field ], start_num, num_chars), returns the characters from the middle of a text string, given a starting position and length.
- PROPER([text field ]), converts and returns a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase.
- LEN([text field ]), returns the number of characters in the specific text field.
- TRIM([text field ]), returns the text field with any trailing white space, such as blank spaces, removed.
- UPPER([text field ]), returns text as an uppercase. This function is applicable only for Text type and ConstantText type only.
- VALUE([text field ]), converts a text string representing a number into the number (double) format. It supports percentages, currencies, scientific notations, and negative numbers.
- LOWER([text field ]), returns text as a lowercase. This function is applicable only for Text type and ConstantText type only.
- FIND([text field1 ], [text field2 ]), returns the starting position of text field1 within text field2. You can also use quotes to specify the text to find in text field2. If the text appears more than once within text field2 , this function returns the starting position of the first occurrence, from left to right.
- LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name }), returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where Key Field Name was found. Key Field Name must exist in ESM ModelName and in the model that contains the LOOKUP function. The Key Field Name that exists in both models must be enclosed with curly braces { }. See Example: Using the LOOKUP Function. To look up a number instead of text, see RANGELOOKUP under Math and Numeric Functions.
- TEXT([date ], format ), returns the date as a text string in the specified format . The argument date must be specified by using a date field, the DATE function, or the result of other formulas. The argument format is the same as the Format column for Date fields and must use uppercase: "MMM-YYYY", "MMM-YY", "DD/MM/YYYY", "MM/DD/YYYY", "DD-MM-YYYY", or "MM-DD-YYYY". Both arguments are required.
Example: TEXT(DATE(1,1,2019), "MMM-YYYY") returns the text string "Jan-2019".
See Example: Using EOMonth, EDate, Min, Max, and Text Functions
Example: Using the LOOKUP Function
The LOOKUP function allows two ESM models to communicate with each other. There must be one matching field in both models, called the Key Field.
In the following example, Account is the Key Field. Models AA1 and BB1 will interact. Model AA1 contains the information that model BB1 will look up.
Here is the list of fields in model AA1.
Here is model AA1's data.
Model BB1 will look up data in model AA1 based on the common key field, Account.
Here is the list of fields in model BB1. Three fields are formulas with LOOKUP functions.
To calculate the LOOKUP functions, you need to load data only into the first field, Account. All other fields are derived.
Copy and paste the accounts into column A, then click Load Data.
Click Refresh and then you will see the remaining columns calculated and filled in.
Explanation
- Reminder: here is the syntax for the LOOKUP function:
LOOKUP("ESM ModelName ", [ReturnFieldName ], {Key Field Name })
- Model BB1 has 4 fields.
- The first field in BB1 is a text field that serves as the Key Field: Account.
- The second field in BB1 is Offset Account and contains the following formula:
LOOKUP("AA1","AAOffset Account", {Account})
Offset Account is derived by matching the Key Field {Account} (from row 1 in BB1, "PM Fee Revenue"), to a field of the same name in model AA1. Once the matching row is found, the value of the field called AAOffset Account from the row where "PM Fee Revenue" was found in AA1 is returned and placed into Field 2 in BB1: Offset Account. - The third field in BB1 is Activity and contains the following formula:
LOOKUP("AA1","AAActivity", {Account})
Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAActivity from AA1 into Field 3 in BB1: Activity. - The fourth field in BB1 is Offset Activity and contains the following formula:
LOOKUP("AA1","AAOffset Activity", {Account})
Offset Activity is derived by looking up the {Account} value (still in row 1 of BB1, "PM Fee Revenue"), in model AA1, and placing the value of AAOffset Activity from AA1 into Field 4 in BB1: Offset Activity.
- This completes the first row in BB1.
- In the second and following rows of BB1, the same method is used to look up the Key Field and derive the values to place into Fields 2-4.
- When a row is encountered in BB1 where the Key Field does not exist in model AA1 ("DEFABC"), no values for Fields 2-4 are created.
Numeric Functions
- Basic arithmetic operators: + - * /
Example: [Unit Price]*[Avg Daily Units] - SUM([numeric field1 ], [numeric field2 ], ... ), returns the sum of a comma-separated list of values.
- AVG([numeric field1 ], [numeric field2 ], ... ), returns the average value of a comma-separated list of values.
- ROUND([numeric field ], n ), returns the value of the numeric field rounded to the n th decimal place.
Example: Round([Total Amount]/12,2) - FLOOR([numeric field ], n ), returns the value of the numeric field rounded down to the nearest integer multiple of n.
Example: FLOOR([MyPrice],0.25) rounds down to the nearest quarter. - CEILING([numeric field ], n ), returns the value of the numeric field rounded up to the nearest integer multiple of n.
Example: CEILING([MyPrice],0.25) rounds up to the nearest quarter. - RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })
or
RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField }, Decimal_Precision )
This function returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where the Key Fields match and where the value of KeyLookupField fits into the range of values between LowRangeFieldName and HighRangeFieldName.
- The Key Field Names are KeyFieldName1, KeyFieldName2, ..., KeyFieldNameN. You must have at least one Key Field Name. The Key Field Name(s) must exist in ESM ModelName and in the model that contains the RANGELOOKUP function. These key field names must be enclosed with curly braces { }. You can use an unlimited number of Key Fields, but a best practice is to use fewer than 10.
- KeyLookupField is a field of type Numeric or Formula (that resolves to a number). It contains the value that you want to look up in the range of values between LowRangeFieldName and HighRangeFieldName.
- LowRangeFieldName and HighRangeFieldName must be of type Numeric or Formula (that resolves to a number) in ESM ModelName.
- LowRangeFieldName uses >= (greater than or equal to) when comparing to KeyLookupField.
- HighRangeFieldName uses < (less than) when comparing to KeyLookupField.
- Ranges should be continuous so they do not leave gaps: 0-5, 5-10, 10-15, and so on.
- If the value of KeyLookupField is below the value of LowRangeFieldName , 0 is returned.
- If the value of KeyLookupField is equal to or above the value of HighRangeFieldName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
- If the value of KeyLookupField does not fit in any range, 0 is returned. This implies that the ranges are not continuous, and the KeyLookupField value falls in a gap between ranges.
- If the value of KeyLookupField is valid in more than one set of ranges (rows) in ESM ModelName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
- Decimal_Precision is optional. It indicates to what decimal place numbers should be compared. In other words, how far out to the right should the KeyLookupField be compared to the low range and high range. For example, if Decimal_Precision is 6, KeyLookupField is 0.12345 and the low range is 0.12 and the high range is 0.123456, then KeyLookupField is found in the range. If not provided, the default is 4. Valid values for Decimal_Precision are integers 0-9.
- HighRangeFieldName has a maximum value of 922 trillion, if using 4 decimal places.
- Lowering the number of decimal places from the default of 4 increases the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
- Increasing the number of decimal places from the default of 4 reduces the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
- As a best practice, using decimal precision greater than 4 is recommended only when the range values are small numbers.
- See Example: Using the RANGELOOKUP Function. To look up text instead of a number, see LOOKUP under Text Functions.
Example: Using the RANGELOOKUP Function
The RANGELOOKUP function allows two ESM models to communicate with each other. There must be one or more matching fields in both models, called the Key Fields.
In the following example, Company and Region are the Key Fields. Models Commission Ranges and Commissions Looked Up will interact. Model Commission Ranges contains the information that model Commissions Looked Up will look up.
Here is the list of fields in model Commission Ranges.
Here is the data for model Commission Ranges.
Model Commissions Looked Up will look up data in model Commission Ranges based on the common key fields, Company and Region.
Here is the list of fields in model Commissions Looked Up.
To calculate the RANGELOOKUP function, you need to load data into the first four fields. The formula fields are derived. Copy and paste the raw data into columns A-D, then click Load Data.
Click Refresh and then you will see the remaining columns calculated and filled in.
Explanation
Reminder: here is the syntax for the RANGELOOKUP function:
RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })
- Model Commissions Looked Up has six fields:
- The first two fields are text fields that serve as the Key Fields: Company and Region.
- The third field is an informational text field: Sales Rep.
- The fourth field is a numeric field that serves as the Key Lookup Field: Sale Amount. This is the number that is used to look in the ranges of numbers in the model Commission Ranges.
- The fifth field is Commission % and contains the following formula:
RANGELOOKUP("Commission Ranges","Comm%", "Sales Amt Low", "Sales Amt High", {Company}, {Region}, {Sale Amt})- Commission % is derived by matching the Key Fields Company and Region (from row 1 in Commissions Looked Up, "Company A" and "West"), to the fields of the same name in model Commission Ranges. In Commission Ranges, "Company A" and "West" are found in row 1.
- Next, the value of Sale Amt is matched to the range of values between Sales Amt Low and Sales Amt High in row 1 of Commission Ranges. If Sale Amt >= Sales Amt Low, and if Sale Amt < Sales Amt High, the range is a match. Since the Sale Amt was 5000 and the Low and High Ranges were 0 to 10,000, then the range is row 1 is a match.
- Next, the value in the field Comm% from the row where the range matches in Commission Ranges is returned and placed into the Commission % field in Commissions Looked Up. The value of Comm % is 0.1 and this is returned to Commissions Looked Up and placed into the field Commission %.
- The sixth field is Commission Amt and it is a formula of Commission % * Sale Amt. This formula takes place within the Commissions Looked Up model and results in 500. To further refine this formula, a rounding function could be added to it: ROUND([Sale Amt]*[Commission %], 2)
The rows of data in Commissions Looked Up demonstrate the rules for the ranges:
Commissions Looked Up | Commission Ranges | |||||||
---|---|---|---|---|---|---|---|---|
Company | Region | Sale Amt |
| Sales Amt Low | Sales Amt High | Returns this Comm% Value | Evaluation | Notes |
Company A | West | 5000 | matches to | 0 | 10000 | 0.1 | 0 <= 5000 < 10000 | The range fits and the Comm% value is returned. |
Company A | West | 10000 | does not match | 0 | 10000 |
| 0 <= 10000 but is not < 10000 |
|
|
|
| matches to | 10000 | 20000 | 0.15 | 10000 <= 10000 < 20000 | The range fits and the Comm% value is returned. |
Company A | West | 21000 | is higher than | 10000 | 20000 | 0.15 | 10000 <= 21000 but is not < 20000 | The lookup is higher than the range, and the Comm% is returned. |
Company A | East | 14999.5 | matches to | 0 | 15000 | 0.12 | 10000 <= 14999.5 < 15000 | The range fits and the Comm% value is returned. |
Company B | West | 7500 | is lower than | 10000 | 20000 | 0 | 7500 < 10000 | The lookup is below the range, so 0 is returned. |
Company C | East | 16000 | matches to | 0 | 16001 |
| 0 <= 16000 < 16001 |
|
|
|
| also matches to | 16000 | 21000 | 0.15 | 16000 <= 16000 < 21000 | The lookup fits in two ranges, so the highest range Comm% is returned. |
Company C | East | 16000.5 | matches to | 0 | 16001 |
| 0 >= 16000.5 < 16001 |
|
|
|
| also matches to | 16000 | 21000 | 0.15 | 16000 >= 16000.5 < 21000 | The lookup fits in two ranges, so the highest range Comm% is returned. |
Company C | East | 16001 | does not match | 0 | 16001 |
| 0 >= 16001 but is not < 16001 |
|
|
|
| matches to | 16000 | 21000 | 0.15 | 16000 >= 16001 < 22000 | The range fits and the Comm% value is returned. |
Company C | West | 5000 | does not match | 0 | 5000 |
| 0 >= 5000 but is not < 5000 |
|
|
|
| is lower than | 6000 | 10000 | 0 | 5000 < 6000 | The lookup is between the two ranges provided, so 0 is returned. |
Company C | West | 5500 | is higher than | 0 | 5000 |
| 0 >= 5500 but is not < 5000 |
|
|
|
| is lower than | 6000 | 10000 | 0 | 5500 < 6000 | The lookup is between the two ranges provided, so 0 is returned. |
Company C | West | 6000 | does not match | 0 | 5000 |
| 0 <= 6000 but is not < 5000 |
|
|
|
| matches to | 6000 | 10000 | 0.11 | 6000 <= 6000 < 10000 | The range fits and the Comm% value is returned. |
Example: Using RANGELOOKUP to Compare Dates
If you want to compare Ranges and get a value as the output based on the details configured in the model, you can use the RANGELOOKUP function.
The following use case provides details on how you can use the RANGELOOKUP function to compare Dates and other attributes in the model and get a Value as the output.
For example, let's consider a source model that contains data for a company and its products in a specific region. Suppose you have defined a date range with their respective Start Date and End Date and defined a Value for each date range. The following image shows the model with all data.
When you enter any date for comparing data (as the last lookup field argument), the RANGELOOKUP function uses the attributes defined in the syntax and searches for the date within the date range. If the date falls within any of the Date Range defined and all other attributes match, the RANGELOOKUP function returns the value specified for that Date Range. The low range is inclusive and the high range is exclusive, so 04/01/2021 would return the value
The RANGELOOKUP function validates the date and other model parameters such as region, product, etc., and returns the configured value as the output.
In the above example, when you enter the comparison date as 09/01/2021, the RANGELOOKUP function returns the Value as 3. The function validates all parameters defined as keys in the syntax and returns the Value only if all keys match, that is, Region, Product, Company, and Comparison Date. So, when the comparison date was 09/01/2021, the RANGELOOKUP function validates if the date “09/01/2021” falls in any of the specified Date Range (Start Date and End Date) and also validates if the keys Region - “West”, Product - “Laptop”, Company - “Company A” is available in the Model, and then returns the value “3” configured for the combination.
The following image shows the defined syntax for the function.
If any of the parameters are not available in the Model, the function returns 0 as the value. The last parameter must be the field used for comparison. For example, when the Comparison Date was 11/28/2021 and Region was “East” and Company was “Company B”, the function returned the value 0 as the Model did not contain the Region and Company details.You have the flexibility to define the keys in the function's syntax based on your requirements and model. If the Comparison Date falls below the ranges (12/01/2020), the value returned will be 0. If the Comparison Date falls beyond the ranges (01/01/2022), the value returned will be 4.
- It is recommended that the Date Range should be continuous so that the comparison date falls in one of the category.
- There must be at least one key for the RANGELOOKUP function to process the output value.
Logical Functions
- Basic comparison operators: < > <= >= <>
- IF([expression ], TrueResult , FalseResult ), evaluates the logic of the expressions and returns TrueResult if the expression is true and FalseResult if the expression is false.
- AND([expression1 ], [expression2 ]), returns TRUE if expression1 and expression2 are both true, otherwise returns FALSE.
- OR([expression1 ], [expressions2 ]), returns TRUE if either expression1 or expression2 is true; returns FALSE if both expressions are false.
- ISBLANK([field ]), returns TRUE if the field is blank, otherwise returns FALSE.
- Nested IF, you may nest IF functions within other IF functions. You can nest up to 7 levels.
Date Functions
- Basic arithmetic operators: + -
- YEAR([date ]), returns the year in the date , 0000-9999.
Example: YEAR([datefield]) where datefield points to 11/08/18 returns 2018. - MONTH([date ]), returns the number of the month in the date , 1-12.
Example: MONTH([datefield]) where datefield points to 9/8/18 returns 9. - DAY([date ]), returns the number of the day of the month in the date , 1-31.
Example: DAY([datefield]) where datefield points to 11/08/18 returns 8. - DAYSCOUNT([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), returns the count of days from a Sub-Start Date to the specified "MMM-YYYY".
Example: DAYSCOUNT([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018") - See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle
- DAYSFACTOR([Start Date Field ], [End Date Field ], [Sub-Start Date Field ], [Sub-End Date Field ], "MMM-YYYY"), factor based on the count of days from a Sub-Start Date to the specified "MMM-YYYY" divided by the number of days in that month.
Example: DAYSFACTOR([Planning Start], [Planning End], [Contract Start], [Contract End], "Nov-2018")
See Example: Calculating DaysCount and DaysFactor for a Contract within a Planning Cycle
- DAYS([datefield2 ], [datefield1 ]), returns the number of days between datefield1 and datefield2 . For a positive value, datefield2 should be after datefield1.
- WEEKNUM([date ]), returns the count of weeks from the beginning of the year to the date specified.
Example: WeekNum("2/1/18") returns 5 because Feb 1 falls in the middle of the 5th week of 2018.
- DATE(mm, dd, yyyy ), returns the date in the format MM/DD/YYYY.
- EOMONTH([start_date ], number of months ), returns the date for the last day of the month that is indicated by the number of months before (negative value) or after (positive value) the start_date . The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EOMONTH calculates project end dates that fall on the last day of the month. Both arguments are required.
Example: EOMONTH([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 28, 2019.
See Example: Using EOMonth, EDate, Min, Max, and Text Functions
- EDATE([start_date ], number of months ), returns the date that is indicated by the number of months before (negative value) or after (positive value) the start_date . The argument start_date must be specified by using a date field, the DATE function, or the result of other formulas. The argument number of months must be an integer; any non-integer value is truncated. EDATE calculates project end dates that fall on the same day of the month as the project start date. Both arguments are required.
Example: EDATE([Start_Date], -3) where Start_Date is May 23, 2019 returns Feb 23, 2019.
See Example: Using EOMonth, EDate, Min, Max, and Text Functions
- SUM([datefield ], n), returns the date that is indicated by the number of days, n , added to the datefield . To add to the datefield , n should be a positive number. To subtract from the datefield , n should be a negative number.
See Example: Using Date Arithmetic Functions
Example: Calculating DAYSCOUNT and DAYSFACTOR for a Contract within a Planning Cycle
DAYSCOUNT calculates the number of days that a contract is in progress in a particular month. DAYSFACTOR calculates the percentage of time that a contract is in progress in a particular month. In the following example, assume the following values:
- Contract Start is 03/15/2018 and Contract End is 05/31/2018.
- Planning Start is 01/01/2018 and Planning End is 12/31/2018.
- DAYSCOUNT for Feb-2018 is 0. DAYSCOUNT for Mar-2018 is 17. DAYSCOUNT for Apr-2018 is 30. DAYSCOUNT for May-2018 is 31.
- DAYSFACTOR for Feb-2018 is 0.0. DAYSFACTOR for Mar-2018 is 0.548. DAYSFACTOR for Apr-2018 is 1.0. DAYSFACTOR for May-2018 is 1.0.
Example: Using Date Arithmetic Functions
The following External Source Model uses five input fields and three formulas.
Five fields are inputted:
- Name
- Work Start Date
- Duration in Days
- Bid Amount
- Status
The other three fields are calculated:
- RFP Start Date uses date arithmetic to subtract 30 days from the Work Start Date. This date provides a timeframe for the Request for Proposal to be written.
- Midpoint Date uses date arithmetic to calculate the midpoint date of the project. It uses the input field Duration in Days, divides it in half, and adds that number to the Work Start Date.
- Projected End Date uses the SUM function to demonstrate how to add the input field Duration in Days to the input field Work Start Date.
The external data source contains the following fields.
After loading this data into the External Source Model, the three formula fields are calculated. You can see that the Projected End Date is the Work Start Date plus the Duration. The Midpoint Date is halfway between the Work Start Date and the Projected End Date. The RFP Start Date is 30 days before the Work Start Date.
Example: Converting Transaction Date MM/DD/YY to Separate Fields: Month MM and Year YYYY
In the example below, the source data contains 6 columns (rows 5-10). The Master Model requires that the transaction date (Time) be broken out into separate Month and Year dimensions, so expressions are added that render these fields. Since the expression MONTH[Time] renders as a month number without a leading zero (such as month 1, 2, 3, 10, 11, 12), a constant "0" is concatenated ahead of the month number (such as 01, 02, 03, 010, 011, 012). Then final Month ID is calculated as the right two characters of that string.
Transferring Data from Analytical Model to ESM Model
You can now transfer data from Analytical Model to ESM. Previously, we could transfer Analytical Model data to Analytical Model, and ESM data to ESM or Analytical Model. In the Source Model drop-down list, you can now see Analytical and ESM options which you can map to the Target ESM.
In Practice: To Transfer Data
- In SpotlightXL, select the Model task> External Source Model> Source Map.
- Enter the name of the Source Map and add the description.
- Select the type as Data for loading external source data.
- Select the Analytical model you want to map the target ESM.
- Select any of the following in the Maps To column:
- DimensionFilter: This filter indicates that there is no field present in the Source ESM to map to the Target ESM. You will have to specify a value in the Default Value Column.
- Value: indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.
- Run the Source Map to check the data in the ESM.