问使用Qlik Sense自动日历代码的会计日历

EN

 Dual(Year(Addmonths($1,9)),Makedate(Year(Addmonths($1,9))-1,4,1)) AS [Year] Tagged ('$axis', '$year')
 // e.g. text value will be 2017 and num value will be 01/04/2016 for 01/04/2016 to 31/03/2017

,Dual('Q'&Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1)),Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1),00)) AS [Quarter] Tagged ('$quarter')

,Dual(Year(Addmonths($1,9))&'-Q'&Num(if(Num(Month($1))<=3,4,Ceil(Num(Month($1))/3)-1)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter')

,Month($1) AS [Month] Tagged ('$month')

,Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth')
// Have left this one as-is as generally people want YearMonth to be calendar year

,Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber')
// Have left this one as-is as not sure whether you'd number this for fiscal or calendar weeks 

,Date(Floor($1)) AS [Date] Tagged ('$date')

DERIVE FIELDS FROM FIELDS [StartDate] USING [autoCalendar] ;

REM 
As Financial Year end varies from year to year, set variables for each to determine when each year ends;
Let varFinYear1 = Date#('28/12/2015','DD/MM/YYYY')
Let varFinYear2 = Date#('25/12/2015','DD/MM/YYYY')

Rem 
Generate an entry for every day in your source data table;
Temp:  
Load  
               min(StartDate) as minDate,  
               max(StartDate) as maxDate  
Resident TableNameHere;  

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  

DROP Table Temp;  

TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  

Rem 
Create a map that identifies each year as specified by the variables above, this will need updating for every year;
Map_Calendar:  
Mapping Load  
               TempDate,  
               if(TempDate<=varFinYear1,'Year1'
                ,if(TempDate<=varFinYear2,'Year2','Year3')) as FinancialYear
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

Let varMinDate = null();
Let varMaxDate = null();

Rem Now using our map in the autocalendar we can define non 365 Fiscal years;
[autoCalendar]: 

  DECLARE FIELD DEFINITION Tagged ('$date')
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year')
,Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter')
,Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter')
,Month($1) AS [Month] Tagged ('$month')
,Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth')
,Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber')
,Date(Floor($1)) AS [Date] Tagged ('$date')
,ApplyMap('Map_Calendar',$1) as [FiscalYear]


DERIVE FIELDS FROM FIELDS [StartDate] USING [autoCalendar] ;