# New to QlikView

Discussion board where members can get started with QlikView.

Contributor III

## Payment Period (Days Offset)

Hi

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:

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

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

AUTOGENERATE 1

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

Master_Calendar:

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

Tags (1)
1 Solution

Accepted Solutions
Contributor III

## Re: Payment Period (Days Offset)

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;
2 Replies
Partner

## Re: Payment Period (Days Offset)

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

Contributor III

## Re: Payment Period (Days Offset)

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;