Fiscal Calendar

[mohan2391]

Hi,

I have a master calendar for Fiscal Year Calendar.

Calendar:

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('Link_Date', recno()))-1 as mindate,

max(FieldValue('Link_Date', recno())) as maxdate

AUTOGENERATE FieldValueCount('Link_Date');

SET vFiscalYearStartMonth =11;

SET vFD = 6; 

MasterCalendar:

Load Distinct *,

Date(Date#(TempDate,'MM/DD/YYYY'),'DD/MM/YYYY') as Link_Date,

Dual([FY Quartertest],AutoNumber([FY Quartertest])) as [FY Quarter],

Dual([FY Monthtest],AutoNumber([FY Monthtest])) as [FY Month],

AutoNumber(Cal_Year&'-'&[FY Week]) as [FY WeekID];

Load Distinct *,'FY'&right(Cal_Year,2) as FiscalYear,

Cal_Month&Chr(39)&right(Year,2) as [FY Monthtest],

Cal_Quarter&Chr(39)&right(Cal_Year,2) as [FY Quartertest],

Ceil((TempDate-StartOfFWeekOne+1)/7) as [FY Week];

Load Distinct *,       

        Dual(Month, fMonth) as Cal_Month,

          Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as Cal_Quarter;

LOAD Distinct *,Year + If(Month>=$(vFiscalYearStartMonth), 1, 0) as Cal_Year,

Mod(Month-$(vFiscalYearStartMonth), 12)+1 as fMonth,

WeekStart(FYearStart,0,$(vFD))    as StartOfFWeekOne;

Load  TempDate,

YearStart(TempDate,0,$(vFiscalYearStartMonth)) as FYearStart,

Year(TempDate) As Year,

Month(TempDate) As Month,

Week(TempDate)as ISOWeekNo,

Dual(WeekDay(TempDate),Mod(WeekDay(TempDate-$(vFD)),7)+1) as WeekDay

Resident Calendar where TempDate>=Date(MakeDate(2015,01,01),'DD/MM/YYYY')order by TempDate;

DROP table Calendar;

* We are showing data for 24 months (2 Years)

* In charts if i take dimension as FYWeek, it is showing week numbers from 1 to 53

* On each week it is showing 2 years of data (Ex: For Week 1, it is showing the sum of value for 2 years as week1 comes 2 times for 2 years)

-->> So,  I need  week number along with year as a field (Ex: 2016-1,2015-6 etc.)

How to achieve this ?

Please help me..

Thank You

[vinieme12 · Accepted solution]

MasterCalendar:

Load Distinct *,

FiscalYear&'-'&[FY Week] as FiscalYrWeek,

Date(Date#(TempDate,'MM/DD/YYYY'),'DD/MM/YYYY') as Link_Date,

Dual([FY Quartertest],AutoNumber([FY Quartertest])) as [FY Quarter],

Dual([FY Monthtest],AutoNumber([FY Monthtest])) as [FY Month],

AutoNumber(Cal_Year&'-'&[FY Week]) as [FY WeekID];

Load Distinct *,'FY'&right(Cal_Year,2) as FiscalYear,

Cal_Month&Chr(39)&right(Year,2) as [FY Monthtest],

Cal_Quarter&Chr(39)&right(Cal_Year,2) as [FY Quartertest],

Ceil((TempDate-StartOfFWeekOne+1)/7) as [FY Week];

Load Distinct *,      

        Dual(Month, fMonth) as Cal_Month,

          Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as Cal_Quarter;

LOAD Distinct *,Year + If(Month>=$(vFiscalYearStartMonth), 1, 0) as Cal_Year,

Mod(Month-$(vFiscalYearStartMonth), 12)+1 as fMonth,

WeekStart(FYearStart,0,$(vFD))    as StartOfFWeekOne;

Load  TempDate,

YearStart(TempDate,0,$(vFiscalYearStartMonth)) as FYearStart,

Year(TempDate) As Year,

Month(TempDate) As Month,

Week(TempDate)as ISOWeekNo,

Dual(WeekDay(TempDate),Mod(WeekDay(TempDate-$(vFD)),7)+1) as WeekDay

Resident Calendar where TempDate>=Date(MakeDate(2015,01,01),'DD/MM/YYYY')order by TempDate;

DROP table Calendar;

[vinieme12]

FiscalYrWeek is the new field added

[mohan2391]

Hi,

Working Fine..

But in charts it is showing OUT OF OBJECT MEMORY when i take this field as dimension

[vinieme12]

It's not the problem with th field but the chart itself

[vinieme12]

You might want to post the chart expressions and dimensions on a new thread

[mohan2391]

Hi,

I resolved that issue when selected dimension limits show only 10..

But 1 more issue.

How can i show latest 10 weeks of data ?

We have options to show First 10, Largest 10 & Smallest 10 only in dimension limits.

[vinieme12]

In the chart expressions add set analysis something like

[FY Week] = {'>=$(=max([FY Week])-9)'}

[mohan2391]

No, i have so many charts and expressions in them, i can do it on dimension only.

How to achieve ?

[vinieme12]

Add a calculated dimension with if() ; something like below

if( week >= max week -9 , week)

And check Suppress when value is null on this