Overview

This article will detail storing dates as part of a transaction in Solver Cloud and Private Host. There are a couple ways dates get stored to a transaction dollar amount in Cloud. 

1. 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
2. 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. 
3. A transaction can have and attribute that is defined as a "datetime" attribute (ie document date). This is separate from the period and helps describe/define the transaction that is being stored. 

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

The below section assumes knowledge of Report Designer and how to setup Budgeting Settings. 

Storing to a Period

This is the most common practice is the Solver Product. In budgeting, a typical form is setup to store back to a period. This is done by dragging period into the Budgeting tab within 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 

1. 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. 
2. The underlying properties of a datetime attribute include that a date time value must be provided. But what 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 we can add that to our 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 a empty/NULL

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

Storing to Date

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

To do so, all you need to do is remove period from you budgeting settings and drag in date from the More Fields section of the module. 

Video Tutorial