Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan2391
Creator II
Creator II

Fiscal Calendar

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

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

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;


View solution in original post

9 Replies
vinieme12
Champion II
Champion II

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;


View solution in original post

vinieme12
Champion II
Champion II

FiscalYrWeek is the new field added

mohan2391
Creator II
Creator II
Author

Hi,

Working Fine..

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

vinieme12
Champion II
Champion II

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

vinieme12
Champion II
Champion II

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

mohan2391
Creator II
Creator II
Author

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
Champion II
Champion II

In the chart expressions add set analysis something like

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

mohan2391
Creator II
Creator II
Author

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

How to achieve ?

vinieme12
Champion II
Champion II

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

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

And check Suppress when value is null on this