# 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
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
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.

Specialist

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

Maybe you have to consider Mapping

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

Master III

Like Below?

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

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

QuartersMap:

rowno() as Month,

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

AUTOGENERATE (12);

[Cal]:

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];

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

AutoGenerate(1)

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

Left Join