Hi, I am quite new to QlikView and I would like to create a business plan based on what amounts (based on volume forecasts and prices) are contracted today. I load data which contains information about what is now contracted for different years for different customers. What I want to do is to calculate a plan for the upcoming years based on what volumes are contracted.
For example (fictional numbers):
p, d and a are parameter values for variables based on how many of the customer prolong their contracts and how much the margin can be increased and how many new customers may be acquired.
How can this be done in QlikView? I have tried to make calculations but it doesn't work. In Excel this simple example is easy just by referencing values, but I want to include features so that certain customers that we know are not going to prolong their contracts are excluded from all calculations except for what is left on their existing contracts. Also, some customer contracts end in a month earlier than December which makes calculations harder since the prolongation and development values should then be based on the last 12 months of the contract and then split over the years, i.e. if a contract ends in May the prolonged amount should be 7/12 of the value of the last 12 months for that year.
Yes, I had that thought as well. The problem is not about assigning values to the variables, but getting QlikView to create rows and assigning the correct values for each statement. I thought of doing it directly in expressions but then I realized it can probably only be done in the script.
Here's what I have so far:
for vBudgetYear = $(vFirstBudgetYear) to $(vLastBudgetYear)
$(vBudgetYear) AS DeliverYear,
ChurnGrossProfit * $(vChurn) AS ChurnGrossProfit,
DevelopmentGrossProfit * $(vDevelopment) AS DevelopmentGrossProfit,
AcquisitionGrossProfit * $(vAcquisition) AS AcquisitionGrossProfit
Resident Prolongation Where DeliverYear=$(vBudgetYear) - 1;