Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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;
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
Contributor III
Contributor III

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
Partner

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
Partner

 

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.