What is the use case for pulling in values from a Modeled Sheet Calculated Account into the Timespan of a Modeled Sheet for specific months? Also, how would I do this?
In some cases, it is useful to bring the values of a modeled sheet calculated account into the Timespan of the modeled sheet.
Some use cases are:
1. Revenue / Cash Flows for Sales Orders.
2. Salaries for Employees.
To pull these values into the modeled sheet timespan, we will first need:
1. A row key. See this ARTICLE for more information. In this case, we will use "ID".
2. A calculated account on the modeled sheet. In this example, we will use "Revenue" from the Contract Sales Cash Flows, with sheet code (CSCF).
Let's look at the first use case of placing the revenue / cash flows for sales orders in the timespan. Let's say we have the lump sum of the sale, and want to spread that amount over the number of months entered for the row. Let's also assume that we want to see the values in the timespan starting with the date of the sale through the number of months entered.
We will need to create an additional calculated account to give us a 1 for every month that should have a value in the timespan, and a 0 otherwise. In other words, we want a formula that returns a 1 for the date of sale and the number of months following that date that are entered for the row.
An example would be:
if(versionmonth(this)>=ROW.Versionmonth_Dateofsale and versionmonth(this) <= ROW.versionmonth_extended, 1, 0)
Where versionmonth_dateofsale is the date column and Row.Versionmonth_extended is the Date of Sale + the number of months:
We would then multiply our Revenue calculated account by the versionmonthlookup. For example:
Once we have this calculated account, we just need to bring it into the timespan. Recall that the Row Key for this sheet is "ID". The formula for the timespan would be:
This can be translated as: Look at the total value for the Revenue account for my Contract Sales Cash Flows sheet and return the value for the row that has 20 as the ID. This way, we filter the Revenue results for this specific row. Please see the screenshot below for more examples. The formulas would be: