Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cristian_av
Creator III
Creator 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:

     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;

1 Solution

Accepted Solutions
cristian_av
Creator III
Creator III
Author

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;

View solution in original post

2 Replies
martynlloyd
Partner - Creator III
Partner - Creator III

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
Creator III
Creator III
Author

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;