Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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; |
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...
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; |