Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Master Calendar for Fiscal Year starting April

I am very new to Qlikview. I am still trying to find my way around and to understand QV. I have used the available script to create a Master Calendar.

  1. QuartersMap: 
  2. MAPPING LOAD  
  3. rowno() as Month, 
  4. 'Q' & Ceil (rowno()/3) as Quarter 
  5. AUTOGENERATE (12); 
  6.  
  7. Temp: 
  8. Load 
  9.                min(GLDate) as minDate, 
  10.                max(GLDate) as maxDate 
  11. Resident Orders; 
  12.  
  13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
  14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
  15. DROP Table Temp; 
  16.  
  17. TempCalendar: 
  18. LOAD 
  19.                $(varMinDate) + Iterno()-1 As Num, 
  20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
  21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
  22.  
  23. MasterCalendar: 
  24. Load 
  25.                TempDate AS GLDate
  26.                week(TempDate) As Week, 
  27.                Year(TempDate) As Year, 
  28.                Month(TempDate) As Month, 
  29.                Day(TempDate) As Day, 
  30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
  31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
  32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
  33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
  34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
  35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
  36.                WeekDay(TempDate) as WeekDay 
  37. Resident TempCalendar 
  38. Order By TempDate ASC; 
  39. Drop Table TempCalendar;


This creates the year from January to December. I need some help to change this script so that I get my year to run from April to March and my quarters to be Q1 - Apr-Jun; Q2 - Jul-Sep; Q3 - Oct-Dec; Q4 - Jan-Mar. My data is General Ledger Transaction that I want to reflect in columns and needs to roll over for each financial year.


Thank you for any and all assistance.

4 Replies

Re: Master Calendar for Fiscal Year starting April

Try to add you master Calendar like this, And then take list box object for QuarterName then see

  1. QuartersMap:
  2. MAPPING LOAD 
  3. rowno() as Month,
  4. 'Q' & Ceil (rowno()/3) as Quarter
  5. AUTOGENERATE (12);
  6. Temp:
  7. Load
  8.               min(GLDate) as minDate,
  9.               max(GLDate) as maxDate
  10. Resident Orders;
  11. Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
  12. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
  13. DROP Table Temp;
  14. TempCalendar:
  15. LOAD
  16.               $(varMinDate) + Iterno()-1 As Num,
  17.               Date($(varMinDate) + IterNo() - 1) as TempDate
  18.               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
  19. MasterCalendar:
  20. Load
  21.               TempDate AS GLDate,
  22.               week(TempDate) As Week,
  23.               Year(TempDate) As Year,
  24.               Month(TempDate) As Month,
  25.               Day(TempDate) As Day,
  26.               YeartoDate(TempDate)*-1 as CurYTDFlag,
  27.               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
  28.               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
  29.               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
  30.               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
  31.               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
  32.               QuarterName(AddMonths(TempDate,3)) as QuarterName,
  33.               WeekDay(TempDate) as WeekDay
  34. Resident TempCalendar
  35. Order By TempDate ASC;
  36. Drop Table TempCalendar;
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
girishchhatani1
New Contributor III

Re: Master Calendar for Fiscal Year starting April

Please find the below code snippet:-

SET DateFormat='DD-MM-YYYY';

let varMinDate=num('01-01-2014');

let varMaxDate=num(Today());

UnlinkedCalendar:

load

date($(varMinDate) + rowno() -1, 'DD-MM-YYYY') as [Document Date],

month($(varMinDate) + rowno() -1)as PO_Month,

year($(varMinDate) + rowno() -1) as PO_Year,

day($(varMinDate) + rowno() -1) as PO_Day

autogenerate($(varMaxDate)-$(varMinDate)+1);

UnlinekCal:

load *,

Num([Document Date]) as NumPostingDate,

right(yearname([Document Date],0,4),4) as FYear,

Year([Document Date]) as UYear,

num(month([Document Date])) as UMonth,

MonthName(MakeDate(right(yearname([Document Date],0,4),4),num(month([Document Date])))) as UC_MonthName,

if(num(month([Document Date]))<=3,(num(month([Document Date]))+9),(num(month([Document Date]))-3)) as FIN_MNT

resident UnlinkedCalendar;

Cal:

load *,

'Q' & ceil(num(FIN_MNT)/3) as Quarts,

ceil(num(FIN_MNT)/3) as Quarter,

Left(UC_MonthName,3) as MonthName

Resident UnlinekCal Where FYear >= 2012;

drop table UnlinkedCalendar,UnlinekCal;

Not applicable

Re: Master Calendar for Fiscal Year starting April

Thank you Anil. I got the quarters to reflect correctly. What I am also looking for is when you choose a fiscal year eg. 2016/2017, i require the app to reflect the month Apr 2016 to Mar 2017. Is this possible?

Highlighted
Partner
Partner

Re: Master Calendar for Fiscal Year starting April

Temp: 

Load 

               min(Date) as minDate, 

               max(Date) as maxDate 

Resident [Transaction Table]; 

 

SET vFiscalYearStartMonth = 4; // Number of Month Will be Here for Quarter Start

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

MasterCalendar: 

LOAD   *,

    Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter; // Fiscal Calendar Quarter

Load 

               TempDate AS Date, 

               week(TempDate) As Week, 

               Year(TempDate) As Year,

               Dual(Month(TempDate),

               Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1) AS FiscalMonth,

               YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear,

               Year(AddMonths(TempDate,$(vFiscalYearStartMonth)))  as [Fiscal Year], 

               Month(TempDate) As Month, 

               Day(TempDate) As Day

Resident TempCalendar;

DROP Table TempCalendar;