topic Re: Master Calendar for Fiscal Year starting April in QlikView

Master Calendar for Fiscal Year starting April

Wed, 25 Nov 2020 16:16:04 GMT

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.

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(GLDate) as minDate,
max(GLDate) as maxDate
Resident Orders;
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
TempDate AS GLDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
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.

Re: Master Calendar for Fiscal Year starting April

Anil_Babu_Samineni — Tue, 29 Nov 2016 10:40:01 GMT

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

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(GLDate) as minDate,
max(GLDate) as maxDate
Resident Orders;
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
TempDate AS GLDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
QuarterName(AddMonths(TempDate,3)) as QuarterName,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: Master Calendar for Fiscal Year starting April

Anonymous — Tue, 29 Nov 2016 10:40:18 GMT

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;

Re: Master Calendar for Fiscal Year starting April

Tue, 29 Nov 2016 11:49:07 GMT

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?

Re: Master Calendar for Fiscal Year starting April

bohravanraj — Wed, 12 Jul 2017 03:42:33 GMT

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;

Re: Master Calendar for Fiscal Year starting July to September Q1 and so on

mohammadirfan — Wed, 30 Oct 2019 07:20:43 GMT

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?

Re: Master Calendar for Fiscal Year starting July to September Q1 and so on

Arthur_Fong — Wed, 30 Oct 2019 08:09:13 GMT

Try this app:

Re: Master Calendar for Fiscal Year starting July to September Q1 and so on

Victor_Alumanah — Wed, 30 Oct 2019 11:11:50 GMT

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;

Re: Master Calendar for Fiscal Year starting July to September Q1 and so on

apthansh — Tue, 07 Apr 2020 20:13:14 GMT

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.

Re: Master Calendar for Fiscal Year starting April

Kartik_semwal_99 — Tue, 25 Mar 2025 14:48:03 GMT

Hi @apthansh, Have you found the script for master calendar from July to June ?