Fiscal Year

[soha1902]

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.

[yura_ratu]

Hi,

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

Your calendar aggregation looks correct.

[jonathandienst]

You need a ; before the LOAD...

...

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

LOAD

...

[soha1902]

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.

[soha1902]

Any Help

[jonathandienst]

I suggest you post your application or a representative sample.

Preparing examples for Upload - Reduction and Data Scrambling