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

8 Replies
Anil_Babu_Samineni

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;
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anonymous
Not applicable
Author

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
Author

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?

bohravanraj
Partner - Creator II
Partner - Creator II

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;

mohammadirfan
Contributor
Contributor

Thank you. 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 of July 2016 to June 2017. Is this possible?

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

 

Try this app:

MC.PNG

Victor_Alumanah
Creator
Creator

Replace INVOICE_DATE with your date value

replace the resident table name Customer_Sales with your name

The master calendar should run from July to June

 

StartAndEndDates:
LOAD
MIN(INVOICE_DATE) AS FirstOrderDate,
MAX(INVOICE_DATE) AS LastOrderDate
RESIDENT Customer_sales;


LET vFirstDate = NUM(PEEK('FirstOrderDate', 0, 'StartAndEndDates'));
LET vLastDate = NUM(PEEK('LastOrderDate', 0, 'StartAndEndDates'));


TempCal:
LOAD
DATE($(vFirstDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vLastDate) - $(vFirstDate) + 1;


MasterCalendar:
LOAD
TempDate AS INVOICE_DATE,
WEEK(TempDate) AS Week,
YEAR(TempDate) AS Year,
MONTH(TempDate) AS Month,
Num(Month(AddMonths(TempDate,(6))),00) as FiscalMonth,
DAY(TempDate) AS Day,
WEEKDAY (TempDate) AS Weekday,
DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,
'FY ' & Right(Year(AddMonths(TempDate,(6))),4)as FiscalYear94,
'FY ' & Right(Year(AddMonths(TempDate,(6))),4)as FiscalYear104,
Right(Year(AddMonths(TempDate,(6))),4)as Fiscal_Year,
if(Right(Year(AddMonths(TempDate,(6))),4)=(Right(Year(AddMonths(Today(),(6))),4)-1),if(TempDate<=(today()-365), 1,0)) as LYTD_Flag,
if(Right(Year(AddMonths(TempDate,(6))),4)=(Right(Year(AddMonths(Today(),(6))),4)),if(TempDate<=(today()), 1,0)) as CurrYTD_Flag,
'Q' & CEIL(MONTH(addmonths(addyears(TempDate,1),-6)) / 3) AS FiscalQuarter

RESIDENT TempCal;


DROP TABLES StartAndEndDates, TempCal;

While we teach we learn
apthansh
Creator
Creator

How to calculate number of months in a Fiscal year based on the start date and the End date.

EX: fISCAL YR IS FROM July- June

Start DateEnd DATE
3/1/2018 0:0010/31/2018 23:59
11/1/2018 0:0010/31/2019 23:59
11/1/2019 0:0010/31/2020 23:59

 

1st row has 4 months, 2nd row has 8 Months and that's for 2019

2nd row has 4 months and 3rd row has 8 months and that's for 2020.