topic Re: Calculating Months In Service in Load in QlikView

Calculating Months In Service in Load

Thu, 02 Jun 2016 16:52:43 GMT

hic have a fleet of vehicles that all have an in service date by unit #. I am needing to calculate the number of months they have been in service from their initial in service date so I can calculate fleet average age through previous months. I have an example of what I believe I am looking for below.

Unit	Month/Year	In Service Date	Months In Service
5962501	Aug-15	8/18/2015	0
	Sep-15		1
	Oct-15		2
	Nov-15		3
	Dec-15		4
	Jan-16		5
	Feb-16		6
	Mar-16		7
	Apr-16		8
	May-16		9

Any help would be much appreciated.

Re: Calculating Months In Service in Load

sunny_talwar — Thu, 02 Jun 2016 17:49:35 GMT

How does the raw data look like?

Re: Calculating Months In Service in Load

Thu, 02 Jun 2016 18:17:15 GMT

Unit	In Service Date
22770	11/13/2015
596860	4/30/1999
4381009	11/20/2012
5961567	12/31/2006
5961570	12/31/2006
5961576	11/1/2006
5961625	9/30/2007
5961641	12/31/2007
5961659	5/1/2008
5961660	5/1/2008
5961663	5/1/2008
5961665	5/1/2008
5961666	5/1/2008
5961683	7/20/2008
5961685	8/15/2008
5961686	8/15/2008
5961687	10/1/2008

Re: Calculating Months In Service in Load

sunny_talwar — Thu, 02 Jun 2016 19:24:13 GMT

Like this?

Re: Calculating Months In Service in Load

Thu, 02 Jun 2016 20:09:50 GMT

I believe so. Can you use that expression in the load script?

Re: Calculating Months In Service in Load

effinty2112 — Thu, 02 Jun 2016 23:12:03 GMT

Hi Nathan,

Try this script:

Data:

LOAD * INLINE [

Unit, In Service Date

22770, 11/13/2015

596860, 4/30/1999

4381009, 11/20/2012

5961567, 12/31/2006

5961570, 12/31/2006

5961576, 11/1/2006

5961625, 9/30/2007

5961641, 12/31/2007

5961659, 5/1/2008

5961660, 5/1/2008

5961663, 5/1/2008

5961665, 5/1/2008

5961666, 5/1/2008

5961683, 7/20/2008

5961685, 8/15/2008

5961686, 8/15/2008

5961687, 10/1/2008

];

Earliest:

LOAD Min(Date([In Service Date])) as EarliestMonth Resident Data;

Let vEarliest = PEEK('EarliestMonth',0,'Earliest');

Let vIntEarliestMonth = MonthStart('$(vEarliest)');

Let vIntThisMonth = MonthStart(Today());

DROP Table Earliest;

[In Service Dates]:

LOAD

Unit,

[In Service Date],

MonthStart([In Service Date]) as InServiceMonth,

Floor(MonthStart([In Service Date])) as IntInServiceMonth,

Floor(Date('$(vIntThisMonth)')) as IntThisMonth

Resident Data;

Drop Table Data;

Calendar:

Load

Date,

Floor(Date) as IntDate;

Load

AddMonths('$(vIntEarliestMonth)',IterNo()-1) as Date

AutoGenerate 1 While AddMonths(Date('$(vIntEarliestMonth)'),IterNo()-1) <=Date('$(vIntThisMonth)');

IntervalMatch(IntDate) LOAD Distinct IntInServiceMonth, IntThisMonth Resident [In Service Dates];

Left Join([In Service Dates])

LOAD

* Resident [In Service Dates-1];

DROP Table [In Service Dates-1];

Left Join([In Service Dates])

LOAD * Resident Calendar;

DROP Table Calendar;

Result:

LOAD

Date(Date,'MM/YYYY') as Month,

Unit,

InServiceMonth,

(Year(Date) - Year(InServiceMonth))*12 +(Month(Date) - Month(InServiceMonth)) as [Months In Service]

Resident [In Service Dates];

DROP Table [In Service Dates];

Make this straight table, I've filtered the data to this year to save time, the last expression give the avg age of the fleet.

Month	Count(DISTINCT Unit)	Sum([Months In Service])	Sum([Months In Service]) / Count(DISTINCT Unit)
01/2016	17	1581	93.0
02/2016	17	1598	94.0
03/2016	17	1615	95.0
04/2016	17	1632	96.0
05/2016	17	1649	97.0
06/2016	17	1666	98.0

Cheers

Andrew

Re: Calculating Months In Service in Load

sunny_talwar — Fri, 03 Jun 2016 01:51:20 GMT

May be this:

Table:

LOAD *,

AutoNumber(RowNo(), Unit)-1 as Requirement;

LOAD *,

MonthName(AddMonths([In Service Date], + IterNo() - 1)) as MonthYear

While AddMonths([In Service Date], + IterNo() - 1) <= MonthName(Today());

LOAD * INLINE [

Unit, In Service Date

22770, 11/13/2015

596860, 4/30/1999

4381009, 11/20/2012

5961567, 12/31/2006

5961570, 12/31/2006

5961576, 11/1/2006

5961625, 9/30/2007

5961641, 12/31/2007

5961659, 5/1/2008

5961660, 5/1/2008

5961663, 5/1/2008

5961665, 5/1/2008

5961666, 5/1/2008

5961683, 7/20/2008

5961685, 8/15/2008

5961686, 8/15/2008

5961687, 10/1/2008

];