Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Prolongation Application

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

YearContractedProlongationDevelopmentAcquistionSum
2014600NANANA600
2015400p*(600-400)d*(600-400)a*(600-400)(a+p+d)*200+400=X
2016200p*(X-200)d*(X-200)a*(X-200)Y
2017100p*(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.

3 Replies
senarath
Contributor III

Re: Prolongation Application

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

Not applicable

Re: Prolongation Application

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.

Not applicable

Re: Prolongation Application

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)

Community Browser