budget

[#1]

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

[swuehl]

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.

[#3]

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

[Michiel_QV_Fan · Accepted solution]

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

[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

];