topic Re: Calculating Month Index in QlikView

Calculating Month Index

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculating Month Index

eduardo_sommer — Tue, 07 Mar 2017 19:56:50 GMT

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

Re: Calculating Month Index

Tue, 07 Mar 2017 20:12:34 GMT

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.

Re: Calculating Month Index

ziadm — Tue, 07 Mar 2017 20:44:32 GMT

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

Maybe you have to consider Mapping

Re: Calculating Month Index

eduardo_sommer — Tue, 07 Mar 2017 21:07:45 GMT

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

Re: Calculating Month Index

sasiparupudi1 — Wed, 08 Mar 2017 07:21:58 GMT

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;