Create Forecast based on date and occurence in Property Module

Hi,

I have a Property module of Costs with following line items:

* Amount (Number format)

* Occurency (List-formatted with options One-off, Monthly, Quarterly, Yearly)

* Cost Start (Month Period format)

* Number of Months (Number format)

I want to feed this information into a Calculation model that has Time as a dimension. It should then take the Amount value and depending on other 3 line items populate the amount value accordingly. Example if:

Amount: $100

Occurency: Monthly

Cost Start: January

Number of Months: 12

Then the Calculation module should have 100 on every cell for that year.

Accepted answers

All comments

anirudh

Hi Johan,

In the Property Module, create 2 line items. One formatted by Quarter and one by year; name them Cost Start Quarter and Cost Start Year. Formula for both is PARENT(ITEM(Cost Start))

In the Calculation module, create a number formatted line item; formula:

IF Property Module.Occurency = Occurency List.One Off
THEN IF ITEM(Time) = Property Module.Cost Start
THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Monthly
THEN IF ITEM(Time) >= Property Module.Cost Start AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Quarterly
THEN IF PARENT(ITEM(Time)) >= Property Module.Cost Start Quarter AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE IF Property Module.Occurency = Occurency List.Quarterly
THEN IF PARENT(ITEM(Time)) >= Property Module.Cost Start Year AND ITEM(Time) < Property Module.Cost Start + Property Module.Number of Months THEN Property Module.Amount ELSE 0

ELSE 0

I'm assuming the number of months driver is applied at the month level. Ex: Cost start is set to May 2020, Occurency is set to Quarterly and Number of months set to 4. The amount will be populated for Apr 2020 to Jul 2020

Let me know if this works for you

Regards,
Anirudh

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…