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.
- 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 be stored to a day ("date storage"). In this case, you are choosing to store to a day and not to a period.
- 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
- 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 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.
- 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.