Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Set a different First Month of The Year

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!

1 Solution

Accepted Solutions
Not applicable

Re: Set a different First Month of The Year

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

6 Replies
hrlinder
Honored Contributor

Re: Set a different First Month of The Year

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

MVP
MVP

Re: Set a different First Month of The Year

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)
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
lusaxy81
New Contributor III

Re: Set a different First Month of The Year

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

Not applicable

Re: Set a different First Month of The Year

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

lusaxy81
New Contributor III

Re: Set a different First Month of The Year

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.

Not applicable

Re: Set a different First Month of The Year

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

Community Browser