Prolongation Application

[#1]

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):

Year	Contracted	Prolongation	Development	Acquistion	Sum
2014	600	NA	NA	NA	600
2015	400	p*(600-400)	d*(600-400)	a*(600-400)	(a+p+d)*200+400=X
2016	200	p*(X-200)	d*(X-200)	a*(X-200)	Y
2017	100	p*(Y-100)	d*(Y-100)	a*(Y-100)	Z

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.

Thankful for any help.

[senarath]

Hi,

Basically what I understand is that you want to change p and d values to see the results.

You can use Input Boxes where you can assign a variable in the input box. So in the chart expression, you can multiply by the variable accordingly as below.

Input ox for vP

In the expression, vP*(600-400)

http://www.inquesti.com.br/downloads_inquesti/Qlikview_guia_tutorial.pdf

https://www.youtube.com/watch?v=CTybb8l1a1k

Thanx

Senarath

[#3]

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)

CONCATENATE (Prolongation)

LOAD CustomerID,

$(vBudgetYear) AS DeliverYear,

ChurnGrossProfit * $(vChurn) AS ChurnGrossProfit,

DevelopmentGrossProfit * $(vDevelopment) AS DevelopmentGrossProfit,

AcquisitionGrossProfit * $(vAcquisition) AS AcquisitionGrossProfit

Resident Prolongation Where DeliverYear=$(vBudgetYear) - 1;

next vBudgetYear

Sadly, it does not work.

[#4]

I have also tried to use the Above function, but it only seems to work with the contracted values and not the calculated numbers (X, Y, and Z in my example)