4 Replies Latest reply: Jan 11, 2014 8:55 AM by whiteline _ RSS

    budget

    ralph snijckers


      Hi,

       

      I want to make a maintence budget.

      Startyear is 2013 and end year 2042 (overview of 30 years).

      There are different activities with there own cycle.

      Example:

      Activity 1: each 5 years pump inspection, cost EUR 1.000

      Activity 2: each 8 years tank maintenance, cost EUR 3.500

      Activity 3: .....

       

      Table:

      Activity; Cycle; Cost

       

      I want to see in a pivot table the cost into the year in reference with the cycle.

       

      Activity                   Year  2018    2021    2023    2028    2029    2033    2037   ......   2042

      Pump inspection          1.000                1000    1.000                1.000

      Tank maintenance                     3.500                              3.500                3.500

       

       

      Is it possible that qlikview calculate the year in order to the startdate and cycle

       

      Ralph

        • Re: budget
          Stefan Wühl

          Maybe like this:

           

          Let vStart = 2013;

          Let vEnd = 2042;

           

          LOAD Activity, Cost, Interval, $(vStart)+iterno()*Interval as Year INLINE [

          Activity, Cost, Interval

          Pump Inspection, 1000, 5

          Tank Maintenance, 3500,8

          ] while $(vStart) + iterno()*Interval <= $(vEnd);

           

          to create your maintenance costs over time, then create a simple pivot table in the front end.

            • Re: budget
              ralph snijckers

              Hello swuehl,

               

              Thank you for the quick respond.

              That's exactly what i'm looking for but I don't have an inline table but a data table.

               

              Budget:

              LOAD

              Activity,
              Cost,
              Asset,
              Interval,
              FROM
              [..\..\..\Examlpe\Brondata.xlsx]
              (
              ooxml, embedded labels, table is Budget);

               

              So I have to find out how I get this into you're script.

               

               

               

               

               

               

               

               

              Ralph

                • Re: budget
                  Michiel van de Goor

                  Ralph,

                  Add the statement from swuehl in your excel load.

                   

                  Let vStart = 2013;

                  Let vEnd = 2042;

                   

                  Budget:

                  LOAD

                  Activity,
                  Cost,
                  Asset,
                  Interval,

                  $(vStart)+iterno()*Interval as Year

                  FROM
                  [..\..\..\Examlpe\Brondata.xlsx]
                  (
                  ooxml, embedded labels, table is Budget)

                  while $(vStart) + iterno()*Interval <= $(vEnd);

              • Re: budget
                whiteline _

                Yet another solution:

                 

                Years:

                LOAD

                    rowno()+$(vStartYear)-1 as DateYear

                AutoGenerate(30);

                 

                left join(Years)

                LOAD

                    rowno() as Interval

                AutoGenerate(30);

                 

                Activities:

                NoConcatenate

                LOAD

                    DateYear,

                    Interval

                Resident Years

                Where mod(DateYear-$(vStartYear)+1, Interval)=0;

                 

                drop table Years;

                 

                right join (Activities)

                LOAD * INLINE [

                    Activity, Cost, Interval

                    Pump Inspection, 1000, 5

                    Tank Maintenance, 3500,8

                ];