6 Replies Latest reply: Nov 22, 2017 1:03 AM by Anil Babu RSS

    QlikView - Fiscal Year Calendar

    Andre Moreira

      Hi Guys, Good Afternoon!

       

      The following Master Calendar is working very fine for Classic Calendar purpose:

       

      Trace ==========================;
      Trace Load TAB "Master Calendar";
      Trace --------------------------;
      
      QuartersMap:
      Mapping Load
      RowNo() as Month,
      'Q' & Ceil(RowNo() / 3) as Quarter
      AutoGenerate(12);
      
      Temp:
      Load
      Min([Calendário Mestre.Data]) as minDate,
      Max([Calendário Mestre.Data]) as maxDate
      Resident [Minha Tabela];
      
      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
      
      Drop Table Temp;
      
      TempCalendar:
      Load
      $(varMinDate) + IterNo() - 1 as Num,
      Date($(varMinDate) + IterNo() - 1) as TempDate
      AutoGenerate(1)
      While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
      
      [Calendário Mestre]:
      Load
      Year(TempDate) as [Calendário Mestre.Ano],
      Month(TempDate) as [Calendário Mestre.Mês],
      Day(TempDate) as [Calendário Mestre.Dia],
      ApplyMap('QuartersMap', Month(TempDate), Null()) as [Calendário Mestre.Trimestre],
      WeekDay(TempDate) as [Calendário Mestre.Dia da Semana]
      Resident TempCalendar
      Order By TempDate asc;
      
      Drop Table TempCalendar;
      

       

      But recently I need to add a feature called Fiscal Year Calendar on structure above.

       

      So I'd like a suggestion related:

       

      • How can I start a Quarter based on specific Month? (eg. Q1 = MAR, APR, MAY)

       

      []'s

      Andre Moreira