Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Month Index

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. 

5 Replies
eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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
Specialist
Specialist

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

Maybe you have to consider Mapping

eduardo_sommer
Partner - Specialist
Partner - Specialist

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
Master III
Master III

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;