topic Re: Set a different First Month of The Year in QlikView

Set a different First Month of The Year

Fri, 07 Aug 2015 05:41:17 GMT

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!

Re: Set a different First Month of The Year

Anonymous — Fri, 07 Aug 2015 09:42:28 GMT

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

Re: Set a different First Month of The Year

jonathandienst — Fri, 07 Aug 2015 09:57:44 GMT

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:

zGLYearStartMonth first month of fiscal year
zLastDOM last day of the month of fiscal period (set to 31 or greater if the fiscal periods correspond to calendar months)

Re: Set a different First Month of The Year

Anonymous — Fri, 07 Aug 2015 10:08:08 GMT

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

Re: Set a different First Month of The Year

Mon, 10 Aug 2015 00:03:18 GMT

Hello Gareth, I cannot load .qvw format files. May I take a look at your script?

Re: Set a different First Month of The Year

Anonymous — Mon, 10 Aug 2015 06:54:49 GMT

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.

Re: Set a different First Month of The Year

Wed, 12 Aug 2015 02:51:47 GMT

Thank you all for the time to respond to my query! I found a helpful blog from HIC Fiscal Year. It worked.