Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY

Fiscal and Standard Calendar generation

jagan
Luminary Alumni
Luminary Alumni

Fiscal and Standard Calendar generation

Hi All,

What is Fiscal Calendar?

From Wikipedia:

A fiscal year (or financial year, or sometimes budget year) is a period used for calculating annual ("yearly") financial statements in businesses and other organizations.

Fiscal years vary between businesses and countries. The "fiscal year" may also refer to the year used for income tax reporting.

In India, the government's financial year runs from 1 April to 31 March midnight.

Example: 1 April 2014 to 31 March 2015 for the financial year 2014–2015. It is also abbreviated as FY15.

The United States federal government's fiscal year is the 12-month period ending on 30 September of that year, having begun on 1 October of the previous calendar year. In particular, the identification of a fiscal year is the calendar year in which it ends; thus, the current fiscal year is 2014, often written as "FY2014" or "FY14", which began on 1 October 2013 and which will end on 30 September 2014.

Below script helps in generating the Standard and Fiscal Year Calendar generation, for this you need to configure 3 variables,

vFiscalYearStartMonth - Tells the starting month of the Fiscal Year

vStartDate - Starting date of the Calendar generation

vEndDate - Ending date of the Calendar generation

Script:

----------

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Note: Based on your requirement you can modify the script to arrive new fields like Week, YTD, MTD etc.

Regards,

Jagan.



Comments
Not applicable

Hi Jagan Mohan,

Thank you fro sharing it! It works perfect when using today() function.

Unfortunately it doesn't work when I try to modify the script so that to work with a Date filed (OrderDate) from  MS SQL DB, it fails somewhere.

I can't understand why, because when I load min and max date and then create variables for startDate and endDate, the result looks similar to that if we use today() and today()-1.

(I tried all the mentioned tips above and still doesn't work.)

Probably it is something in the Date format...


The picture below shows the last script I am using and the error:

Untitled.png

0 Likes
Not applicable

I find it out where the mistake was (in the date format - the format in my db is YYYY-MM-DD)

I want to have fix start and end date for the Master/Fiscal Calendar,

So created a TempTable:

Temp:

load date(minDate,'YYYY-MM-DD') AS minDate,  date(maxDate,'YYYY-MM-DD') AS maxDate inline [

minDate, maxDate

'2005-12-30', '2009-06-30'

];

The whole solution is a combination between this video (Creating a Master Calendar - YouTube and its code) and your code for Fiscal Fields.

If anyone wants to test, he must:

1) replace Key_OrderDate field with the name of his  date field in the DB

2) Change the date format if it is different:

3) Change the vFiscalYearStartMonth value if it is different than 7 (July)

gist:a0658c43cce73d0fccd3

0 Likes
easternfish
Contributor III
Contributor III

HI Everyone,

looks like i was able to do this by using the following .....

Set vFM = 4 ;                                                          // First month of fiscal year

Calendar:

Load Dual(fYear-1 &'/'& fYear, fYear) as YearDF,          // Dual fiscal year

         Dual(Month, fMonth)                as MonthDF,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Orders:

Load

billdate,

Year (date(date#(billdate, 'YYYYMMDD'),'YYYY/MM/DD')) as Year,         

Month (date(date#(billdate, 'YYYYMMDD'),'YYYY/MM/DD')) as Month;      

select *

FROM orders;

I hope that this helps!

now my issue is comparing YTD vs LYTD.  any ideas? : - )

thank you jagan and staniTo89 for all your help.

0 Likes
Not applicable

Hi,

Personally I do this with variables, triggers and sets, but I want when somebody select a year to see

the results for the selected year and for the previous one.


If you want static year, e.g.  2014, you can use sets only:

=sum$<OrderYear={2014}>} Sales)



If you want a dynamic selection you can do the following:

//OrderYear is my date field =Year(DateField)

1) Create 2 variables:

-vStartDate - to show the selected (current year)

-vEndDate - to show the previous year (last year)

// I use these variables in sets, so in order sets to to work properly - add actions for the variables

2) Then in Settings->Document Properties->Triggers->Variable Event Triggers

- for vStartDate Add Action (OnChange)-> Selection (Action Type)-> Select in Field (Action)->

Field: Order Year (OrderYear is the field, containing only the year from my Date field)

Search String: vStartDate

- for vEndDate Add Action (OnInput)-> Selection (Action Type)-> Select in Field (Action)->

Field: Order Year

Search String: vStartDate+1

//This step can be skipped, but I have a list box and want to select a year and that change to reflect on the visualization, so again - Add Event Trigger (for OrderYear field)

3) Next in Settings->Document Properties->Triggers->

-add Action in Field Event Triggers OnSelect for the date field (OrderYear)

-Add->External->Set Variable

-in the Variable field type vStartDate, Value=min(OrderYear)

actions.PNG

Do the same for vEndDate, but the Value is =min(OrderDate)-1


4) Eventually create 2 expressions in a chart (I use Line Chart)

My Expressions in the chart are:

- YTD:

sum({$<OrderYear  = {$(vStartDate)}>}Sales)

//Weher vStartDate is the variable for the Selected year

- LYTD:

sum({$<OrderYear = {$(vEndDate)}>}Sales)

//Where vEndDate is the variable for the Previous year

Regards,

Stani

0 Likes
ziadm
Specialist
Specialist

Hi

I need the Fiscal Month Name generated in this Format having the Dual function to sort the FiscalMonthName in the right order

Apr 2014/2015,

May 2014/2015

Jun 2014/2015

July 2014/2015

Aug 2014/2015

Sep 2014/2015

Oct 2014/2015

Nov 2014/2015

Dec 2014/2015

Jan 2014/2015

Feb 2014/2015

March 2014/2015

Apr 2015/2016


Appreciate your feed back


0 Likes
ziadm
Specialist
Specialist

After so many tries I found the solution

               Dual(Date(MonthEnd(TempDate), 'MMM') & YearName(TempDate, 0, $(vFM)),Date(MonthEnd(TempDate),'MMMYYYY'))   as FiscalMonthName

//Fiscal Month Name i e Apr2014-2015

0 Likes
Not applicable

Hi Jagan,

I want to generate 13 periods for each year, each period contains 28 days, and my fiscal year starts at May 30 every year , i have tried with days offset    vdaysoffset='(Addmonths(Date,4)-Date)+30'

load *,

num(Date)+$(vdaysoffset) as Datenum,

$(vdaysoffset) AS daysoff;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1

however i didn't get the desired results for this, can you please help me on this.

0 Likes
nareshthavidishetty
Creator III
Creator III

Hi Jagan,

Below is the script used  to get Fical Cal,

SET vFiscalYearStartMonth = 2;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

Join(APP)

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Application_Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Application_Date) AS Year, // Standard Calendar Year

Month(Application_Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Application_Date)/3), Ceil(Month(Application_Date)/3)) AS Quarters,  // Standard Calendar Quarter

Mod(Month(Application_Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Application_Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Datee,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

But am getting an error as below

Untitled.png

0 Likes
jagan
Luminary Alumni
Luminary Alumni

HI,

Replace Application_Date with Datee it will work.

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Datee,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Or change Datee with Application_Date.

Hope this helps you.

Regards,

Jagan.

Not applicable

Hi Jagan,


I am new to Qlikview,

Now, I load the financial period by importing excel file,

I think it will work effective if it is wriiten by script.


could you mind sharing your script

if the financial period ranged from 29th of current month to 28th of the next month?


for example,


29-Dec-2015 to 28-Jan-2016 is 2016Jan

29-Jan-2016 to 28-Feb-2016 is 2016Feb

.....

29-Dec-2016 to 28-Jan-2017 is 2017Jan


Thank you

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-09-01 10:30 PM
Updated by: