Financial Periods In Master Calender

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the below script for the Master Calender, However, id like to add financial periods. Ive gotten it right to identify the Quarters correctly for the first 3 by adding Rowno()+2 , as my Financial Period starts in March. if i select Q4, only december is selected, it needs to select the next year's jan and feb aswell. Can anyone assist?

My code below:

QuartersMap:

MAPPING LOAD

RowNo()+2 as Month, //have added +2 to enable it to start at march

'Q' & Ceil(Rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

LOAD

MIN(TRANSACTION_DATE) as MinDate,

MAX(TRANSACTION_DATE) as MaxDate

Resident TRANSACTIONS;

LET varMinDate = num(peek('MinDate',0,'Temp'));

LET varMaxDate = num(peek('MaxDate',0,'Temp'));

DROP Table Temp;

TempCalender:

LOAD

$(varMinDate) + IterNo()-1 as Num,

Date ($(varMinDate)) + IterNo()-1 as TempDate

AutoGenerate 1 while $(varMinDate) + IterNo()-1 <= $(varMaxDate);

MasterCalender:

LOAD

TempDate as TRANSACTION_DATE,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

YearToDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

ApplyMap('QuartersMap',month(TempDate), Null()) as Quarter,

Week(WeekStart(TempDate)) & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalender

Order by TempDate ASC;

Drop Table TempCalender;

Please can anyone assist?

Thanks in advance.

Re: Financial Periods In Master Calender

sunny_talwar — Thu, 11 May 2017 18:35:04 GMT

What you need is a fiscal year calendar... look here: Fiscal and Standard Calendar generation

topic Financial Periods In Master Calender in QlikView

Financial Periods In Master Calender

Re: Financial Periods In Master Calender