Calculating Months In Service in Load

[#1]

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.

[sunny_talwar]

How does the raw data look like?

[#3]

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

[sunny_talwar]

Like this?

[#5]

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

[effinty2112]

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

[sunny_talwar]

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

];