Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

cristian_av
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:

     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;

Tags (1)
1 Solution

Accepted Solutions
cristian_av
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
Highlighted
Partner
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...

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