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

# budget

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

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

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:

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

Ralph,

Let vStart = 2013;

Let vEnd = 2042;

Budget:

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

Yet another solution:

Years:

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

AutoGenerate(30);

left join(Years)

rowno() as Interval

AutoGenerate(30);

Activities:

NoConcatenate

DateYear,

Interval

Resident Years

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

drop table Years;

right join (Activities)