topic Re: budget in QlikView

budget

Sat, 11 Jan 2014 13:05:38 GMT

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

swuehl — Sat, 11 Jan 2014 13:16:11 GMT

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

Sat, 11 Jan 2014 13:29:58 GMT

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_QV_Fan — Sat, 11 Jan 2014 13:50:59 GMT

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 — Sat, 11 Jan 2014 13:55:46 GMT

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

];