3 Replies Latest reply: Nov 25, 2014 10:19 AM by Oskar Ericsson RSS

    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.