Forecast Calculation in Qlikview

Ankhi — Tue, 04 May 2021 14:11:50 GMT

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

Re: Forecast Calculation in Qlikview

Ankhi — Tue, 04 May 2021 14:15:21 GMT

The Qlikview table structure looks like this:

topic Forecast Calculation in Qlikview in App Development

Forecast Calculation in Qlikview

Re: Forecast Calculation in Qlikview