topic Re: Payment Period (Days Offset) in QlikView

Payment Period (Days Offset)

cristian_av — Mon, 21 Jul 2014 14:01:22 GMT

I'm trying to do a master calendar with a "payment period", similar to a fiscal year, but in months.

What I need to do is:

21/03/2014 to 20/04/2014 is Month 04/April

21/04/2014 to 20/05/2014 is month 05/May

and so on...

How can I do it?

I've try that doing a sum to the month... but didn't get what I expected.

Thanks!

SET vDiaEP = 20;

LET vDateMin = 45000;//&Num(Peek('Start_Date_AP', 0, 'AP_Link_Table'));

LET vDateMax = 50000;//Num(Peek('End_Date_AP', -1, 'AP_Link_Table'));

LET vDateToday = Num(Today());

TempCalendar1:

LOAD

$(vDateMin) + RowNo() - 1 AS Date_Key,

Date($(vDateMin) + RowNo() - 1) AS Date

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Master_Calendar:

LOAD Date_Key

,Date

,month(Date)

as Month

,MonthName(Date) as MonthName

,year(Date)

as CalendarYear

,WeekDay(Date) as WeekDay

,month(Date)+If(Day(Date)>$(vDiaEP) and month(Date)<12,1,0)

as MesEP

,month(Date+1) as MesEPSuma

Resident TempCalendar1;

DROP Table TempCalendar1;

Re: Payment Period (Days Offset)

martynlloyd — Mon, 21 Jul 2014 14:19:09 GMT

There are errors in your IF logic, replace your if with something like

if(Day(Date)>$(vDiaEP) and month(Date)<12, month(Date)+1, month(Date) as MesEP

month(Date)+1 as MesEPSuma

The plus 1 goes outside the brackets...

Re: Payment Period (Days Offset)

cristian_av — Mon, 21 Jul 2014 19:39:49 GMT

Thanks!

But that way has trouble when the month is "12"

Finally I solved it with:

		LOAD *
		,dual(MonthName(PeriodoEpNum)&'(ep)',PeriodoEpNum) as MesAñoEp
		,dual(Month(PeriodoEpNum)&'(ep)',num(Month(PeriodoEpNum))) as MesEp;

Master_Calendar:

LOAD Date_Key

// Primary Key, conecta la fecha con los consumos

,Date

,month(Date)	as Mes
,MonthName(Date) as MesAño	//Dual: (abr-2014, numero)(1° dia de abril 2014)
,year(Date)	as Año

,WeekDay(Date) as DiaSemana

	,if(Day(Date)>=$(vDiaEP) , monthstart(addmonths(Date,1)), monthstart(Date))	as PeriodoEpNum //
	Resident TempCalendar1;
	DROP Table TempCalendar1;
	DROP Field PeriodoEpNum FROM Master_Calendar;