topic Re: Fiscal Year in QlikView

Fiscal Year

soha1902 — Mon, 17 Aug 2015 12:11:15 GMT

Can Anyone help me to generate Fiscal Year calender.

My Fiscal Year will be by July - Jun.

I tried following script:

SET vFiscalYearStartMonth = 7;

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

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,

Year & '-' & Month as Key; // Fiscal Calendar Month Name

LOAD

Year(Date) AS 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,

LOAD

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

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

AutoGenerate vEndDate - vStartDate + 1;

It generates the calender but I am not getting expected result in charts.

Thanks.

Re: Fiscal Year

yura_ratu — Mon, 17 Aug 2015 12:51:42 GMT

Hi,

Please provide information what exactly you get and what you expect to get.

Your calendar aggregation looks correct.

Re: Fiscal Year

jonathandienst — Mon, 17 Aug 2015 13:21:03 GMT

You need a ; before the LOAD...

...

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,

LOAD

...

Re: Fiscal Year

soha1902 — Tue, 18 Aug 2015 03:45:36 GMT

I have 2 Ficals Year of Data. July 2013 to June 2015.

Now when I am trying to Year on Year Comparison then Its not showing correct result.

Can any one suggest me, How should I write Current Year expression and Previous Year expression, so my chart will get updated dynamically based on the Year selection.

I am using personal edition so plz share the script.

Thanks.

Re: Fiscal Year

soha1902 — Tue, 18 Aug 2015 04:41:21 GMT

Any Help

Re: Fiscal Year

jonathandienst — Tue, 18 Aug 2015 05:05:00 GMT

I suggest you post your application or a representative sample.

Preparing examples for Upload - Reduction and Data Scrambling