Master Calendar for Fiscal Year starting April

[#1]

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.

[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);
7. Temp:
8. Load
9.               min(GLDate) as minDate,
10.               max(GLDate) as maxDate
11. Resident Orders;
13. Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
15. DROP Table Temp;
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);
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.               QuarterName(AddMonths(TempDate,3)) as QuarterName,
37.               WeekDay(TempDate) as WeekDay
38. Resident TempCalendar
39. Order By TempDate ASC;
40. Drop Table TempCalendar;

[Anonymous]

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;

[#4]

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]

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]

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]

Try this app:

[Victor_Alumanah]

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;

[apthansh]

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 Date	End DATE
3/1/2018 0:00	10/31/2018 23:59
11/1/2018 0:00	10/31/2019 23:59
11/1/2019 0:00	10/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.