Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am trying to do a quarterly/monthly/yearly forecast of Contracts data. This query is about Quarterly forecast.
The fields available are Frequency= (MTH/YR/QTR), Offset_Months=(Precalculated field--values like , 1,0,-1,3 etc),
Next_Due_Date a Date field(values like - 01/08/2021,01/07/22 etc).
I need to create 48 expression as the forecasting needs to be for 4 years and the forecast table always start from the next month of the current date. Screenshot of sample table below.
Label of the 48 expressions are..
Date((addmonths(Today(),1)),'MMM-YY'),Date((addmonths(Today(),2)),'MMM-YY') .....Date((addmonths(Today(),48)),'MMM-YY')
Now for Quarterly calculation based on the Next Due date and offset Months, I have to decide the starting month and drop in the unit price. (Unit Price predefined field QVD)
For e.g if the Next Due Date is 01/08/2021 and offset Months is -1, I have to start forecasting at JuLy -21 and drop in the unit price(predefined field) for that specific Contract line.
Expression I am using to determine this is below:
if(
date(addmonths([CONTRACT_LINES.Next_Due],[CONTRACT_LINES.Offset_Mnth]),'MMM-YY')=
date(addmonths(Monthstart(Today()),1),'MMM-YY')
and (Date((addmonths(Today(),1)),'MMM-YY') - Date(Today(),'MMM-YY')<=[Remaining_Days])
,[Unit Price]...
Remaining_Days = Pre calculated field to determine till which year-month the Contract Line should be forecasted.
For e.g if it is 365 , forecasting should only be for 1 year , if it is 700 forecasting should be for 2 years... etc. Can be ignored for the time being.
Using the above calculation , I have been able to find out the starting month for forecast.
Now based on the Starting Month, in case of quarterly forecast, the next forecast will be Starting Month + 3,So if the starting month was
Jun 21 next forecast will Sep-21, and then the next one will be Dec-21. If the starting Month is Jul-21, next forecast will be Oct-21 and the next one Jan-22.. in this way.
But I am not sure how to achieve this . Can you please help me in developing an expression for the above case?
Sample data for Quarterly Calculation:
Many Thanks in advance
