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.
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.
=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 behavior 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.
