PLAN PH/Cloud How-To: Store back to dates

Summary:

This article details storing/saving back dates as part of a transaction in Solver Cloud and Private Host. There are a few ways dates get stored to a transaction dollar amount in Cloud: 

• A transaction is stored to a Period (ie 202101). In your Data Warehouse you have defined the calendar dates associated to this Period as Period Start and Period End.
• A transaction can have an attribute that is defined as a "datetime" attribute (e.g. document date). This is separate from the period and helps describe/define the transaction that is being stored. 
• A transaction can be stored to a day ("date storage"). In this case, you are choosing to store to a day and not to a period. 

At the very bottom of this article is a video demonstrating the concepts described in this article. 

Article Sections:

• Method
  • Storing a Period
  • Storing to a Date Attribute (Module Attribute)
  • Storing to Date
  • Video Tutorial
• Properties

Method:

The following sections assume knowledge of Report Designer and how to set up Budget Settings. 

Storing to a Period

This is the most common practice in the Solver Product. In Budgeting, a typical form is set up to store back to a period. This is done by dragging Period into the Budgeting tab within the Layout Editor. 

Even though a period is YYYYMM, a transaction must be stored back to a date. When mapping Period, the application will store the budgeted number back to the first calendar day associate to the Period. 

Storing to a Date Attribute (Module Attribute)

Sometimes a date attribute is created to help define/describe a transaction, such as document date. In this case there are a couple things to be aware of:

• Excel inherently serialized dates to allow for Excel formulas to perform calculations. This will convert what you type into Solver from 1/1/2020 to an integer (serialized) value. This integer value is an invalid datetime and will result in a storage error. You need to convert the date time to a YYYYMMDD string to ensure that date attributes get stored back. 
• The underlying properties of a datetime attribute include that a date time value must be provided. But if all transactions being stored back do not require a date, you need to take this into consideration with your Excel formula to account for when dates are not typed in. The equivalent of NULL is 1901-01-01, so you can add that to your excel formula. 

Below is an example of the Excel formula in use:

  =IF(<cellRef>="","19000101",(YEAR(<cellRef>)*10000)+(MONTH(<cellRef>)*100)+DAY(<cellRef>))

• cellRef references the date attribute.
• With this IF case, if the user does not provide a date, 1900-01-01 will be the value passed off to the application, which will store an empty/NULL.

Make sure you update your Storage reference to the new, calculated cell. 

Storing to Date

Finally, and less common practice is storing to Date, not to be confused with storing to a date attribute as described above. Storing to Date still references the Period dimension but is overriding the default behaviour of storing to the first day of the month. In this case, you want to budget to a specific day. 

To do so, you need to remove Period from you budget settings and drag in Date from the More Fields section of the module. 

Video Tutorial 

Properties:

Private Host

Cloud