Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
FiscalYrWeek is the new field added
Hi,
Working Fine..
But in charts it is showing OUT OF OBJECT MEMORY when i take this field as dimension
It's not the problem with th field but the chart itself
You might want to post the chart expressions and dimensions on a new thread
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.
In the chart expressions add set analysis something like
[FY Week] = {'>=$(=max([FY Week])-9)'}
No, i have so many charts and expressions in them, i can do it on dimension only.
How to achieve ?
Add a calculated dimension with if() ; something like below
if( week >= max week -9 , week)
And check Suppress when value is null on this