Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
Please share sample data what is the input and what is expected output.
Maybe you have to consider Mapping
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
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;