topic Re: Fiscal Year Calendar in QlikView

Fiscal Year Calendar

Tue, 09 Dec 2014 07:16:30 GMT

Hi Everyone

I came across and used code for the generation of a Fiscal Calendar from a previous post. There are a few lines of code though that i need a little clarity on as i want to be able to understand and explain it properly. Can anyone explain what they actually mean please. I left only the lines of code that i need explaining on.

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

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;

Regards,

Magen

Re: Fiscal Year Calendar

sushil353 — Tue, 09 Dec 2014 07:29:17 GMT

Hi,

1. Using Dual function we can assign two values to a field .. here FiscalQuarter will get text value of quarter for representation and numerical value underneath.

2. Mod(Month...............) : this is to set the starting month of your fiscal calendar.. $(vFiscalYearStartMonth) has to set before execution of script e.g: if April is the starting month of your fiscal calendar then set variable value to 4 then FiscalMonth get the value 1 for April month...

Same this is happening with YearName................

3. the last set of code is to generate a sequence of date in the range of vStartDate and vEndDate.

HTH

Sushil

Re: Fiscal Year Calendar

jagan — Tue, 09 Dec 2014 07:43:03 GMT

HI,

Check the explanation below

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

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month -- Calculating the fiscal month, assume that Fiscal Year starts on April, then current month is December, then

(12 - 4)/12 + 1 = 9, 9th month of the fiscal year.

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year -- Gives the Fiscal year name based on the fiscal year starting month -- Refer qlikview help file for explanation

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date, -- Generate date field by adding rownum to mindate

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

AutoGenerate vEndDate - vStartDate + 1; -- Looping for each date between min and max date

Regards,

Jagan.

Re: Fiscal Year Calendar

Wed, 18 Feb 2015 11:43:32 GMT

Hi Jagan,

Is there a way to find out first date of fiscal year if my fiscal year is starting from October every year?

thanks for help in advance.

Thanks,

Varsha

Re: Fiscal Year Calendar

jagan — Mon, 23 Feb 2015 02:10:15 GMT

Hi,

Try like this

YearStart(DateFieldName, 0, 10)

-- Where 10 is october.

Regards,

Jagan.