Payment Period (Days Offset)

[cristian_av]

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:

     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;

[martynlloyd]

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

[cristian_av · Accepted solution]

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;