Calculating Month Index

[#1]

I'm trying to calculate a month index (index transactions to time zero) based on a few fields in my data load script.  I have a billing_date field and a min_service_activation date field.  I'm just trying to calculate a month index between the billing date and the service activation date.  I've tried a bunch of variants of calculating the different, but nothing has worked. 

[eduardo_sommer]

Hi, Ryan

If I understood your question you can use the following expression:

(year(billing_date)*12+month(billing_date)) - (year(min_service_activation_date)*12+month(min_service_activation_date))

Eduardo

[#3]

That doesn't work.  For example, a billing_date of 2017-11-01 and a min_s_act_date if 2017-7-22 gives a number of 661393 when it should be 100.

[ziadm]

Please share sample data what is the input and what is expected output. 

Maybe you have to consider Mapping

[eduardo_sommer]

Hi, Ryan

2017*12 = 24204

24204 + 11 = 24215

2017*12 = 24204

24204+7 = 24211

24215 - 24211 = 4 (which is the month index)

100 is the number of days between the dates, which can be obtained by a simple subtraction of the dates

Is this correct?

Eduardo

[sasiparupudi1]

Like Below?

let maxdate=Num(MakeDate(2017,1,1));

let mindate=Num(MakeDate(2007,1,1));

QuartersMap:

MAPPING REPLACE LOAD

    rowno() as Month,

    'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

[Cal]:

LOAD

     TempDate as _KeyScoringDate,

     Date(TempDate,'YYYY-MM-DD') as [Date],

     week(TempDate) As [Week],

     Year(TempDate) As [Year],

     Month(TempDate) As [Month],

     Day(TempDate) As [Day],

     YeartoDate(TempDate)*-1 as [CurYTDFlag],

     YeartoDate(TempDate,-1)*-1 as [LastYTDFlag],

     inyear(TempDate, Monthstart(Today()),-1) as [RC12],

     date(monthstart(TempDate), 'MMM-YYYY') as [MonthYear],

     date(monthstart(TempDate), 'YYYYMM') as [Period],

     ApplyMap('QuartersMap', month(TempDate), Null()) as [Quarter],

     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [WeekYear],

     WeekStart(TempDate) & ' to ' & WeekEnd(TempDate) as [Week Range],

     WeekEnd(TempDate) as [WeekEnding],

     WeekStart(TempDate) as [WeekStarting],

     WeekDay(TempDate) as [Week Day];    

LOAD

     Date($(mindate) + IterNo()) as TempDate

     AutoGenerate(1)

WHILE ($(mindate) + IterNo())-1 <= $(maxdate);

Left Join

load Distinct [Period],

autonumber([Period]) as MonthIndex

resident Cal;