Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Community,
I want to assign a different start month that would align to our Fiscal Year, ours starts every July.
Are there other ways to change the first month of the year?
The way I did is by SQL script like this one:
.....CASE
WHEN DATE(CONVERT_TZ(j.created_at,"+00:00","+10:00")) BETWEEN '2007-07-01' AND '2008-06-30' THEN 2008
WHEN DATE(CONVERT_TZ(j.created_at,"+00:00","+10:00")) BETWEEN '2008-07-01' AND '2009-06-30' THEN 2009.....
and
.....CASE
WHEN MONTH(CONVERT_TZ(j.created_at,"+00:00","+10:00")) =1 THEN 7
WHEN MONTH(CONVERT_TZ(j.created_at,"+00:00","+10:00")) =2 THEN 8.....
I am having a doubt that eventually this would lead to data errors.
Thanks!
Thank you all for the time to respond to my query! I found a helpful blog from HIC Fiscal Year. It worked.
if you have a mastercalendar you may add columns for fiscal month, fiscal year (one way by inline table)
in your Charts you use the fiscal month as dimension
I use this code in my master calendar to get the Fiscal Year and Period from the calendar date (CalDate / CalMonth / CalYear):
// Fiscal year fields - GL Year
CalYear + If(CalMonth < $(zGLYearStartMonth), 0, 1) As GLYear,
// Fiscal year fields - GL Period
If (CalMonth >= $(zGLYearStartMonth),
If(CalDay > $(zLastDOM), CalMonth - $(zGLYearStartMonth) + 2, CalMonth - $(zGLYearStartMonth) + 1),
If(CalDay > $(zLastDOM), CalMonth + (12 - $(zGLYearStartMonth) + 2), CalMonth + (12 - $(zGLYearStartMonth) + 1))) As GLPeriodM,
The variables:
Hi,
Attached is a calendar. in the calendar tab is a variable in which you change the first month of a fiscal year
remember to set the Calendar Type to "always only 1 selected"
essentially you are duplicating your master calendar for different calendar types and then selecting the calendar type you want to work with.
Since all your time data calculations and graphs will work with the new YEAR and MONTH set up - things will automatically change based on the calendar type selections.
hope this helps,
Gareth
Hello Gareth, I cannot load .qvw format files. May I take a look at your script?
Hi May,
you put the following field in your transactions table (or whatever table contains the date):
Year(date(SaleDate))&num(Month(date(SaleDate)),'00') as YM,
then you can add this statement at the end of your load script (or anytime after the table containing YM)
//Create fiscal year start month
set vFiscalMonth = 8;
//create temp calander for all the distinct year month dates in your data
TCAL:
load distinct YM,
num(right(YM,2)) as TMonth,
Month(MakeDate(Left(YM,4),right(YM,2) , 1)) As MONTH
Resident TRANSACTIONS;
CALENDAR:
load
YM,
num(left(YM,4)) as YEAR,
TMonth as MonthSort,
MONTH,
'Q'&(if(num(right(YM,2))<4,1,if(num(right(YM,2))<7,2,if(num(right(YM,2))<10,3,if(num(right(YM,2))<13,4))))) as Quarter,
'Calendar' as CalendarType
resident TCAL;
//Duplicates calendar but for Fiscal Year
Concatenate(CALENDAR)
load
YM,
if(TMonth<$(vFiscalMonth),num(left(YM,4)),num(left(YM,4))+1) as YEAR,
if(TMonth<$(vFiscalMonth),TMonth+12-$(vFiscalMonth)+1,TMonth-$(vFiscalMonth)+1) as MonthSort,
MONTH,
'Q'&(if(num(right(YM,2))<4,4,if(num(right(YM,2))<7,1,if(num(right(YM,2))<10,2,if(num(right(YM,2))<13,3))))) as Quarter,
'Fiscal' as CalendarType
resident TCAL;
Drop Table TCAL;
Once you are using your data in the front end, remember to use YEAR and MONTH when you deal with date information.
NB: create a listbox for CalendarType - select 1 , then right click properties and change to "Always only 1 selected" - this is because multiple calendars duplicate data due to there being both a Fiscal YEAR MONTH and a Calendar YEAR MONTH pointing to the same data so you want to restrict it to only 1 set at all times.
hope this helps.. let me know if you have problems
Gareth
p.s. The MonthSort field is there for if you want to make the list boxes sort the order based on the calendar type. so for fiscal it will go month AUG,SEP,OCT,NOV,DEC,JAN,...JUL and for normal Calendar it will go JAN,FEB,MAR...DEC - also handy for when you make charts.
Thank you all for the time to respond to my query! I found a helpful blog from HIC Fiscal Year. It worked.