topic Re: Fiscal Periods in QlikView

Fiscal Periods

Tue, 30 Jul 2013 08:12:14 GMT

We use financial periods rather than calendar periods for our financial reporting. I have seen some discussion on this, however all these appear to based around a month offset from calendar to financial period. Our issue is that we have financial periods that vary year on year, example of the last two year shown below

Period Year	Period	Period Start Date	Period End Date
2011	1	2011-10-02 00:00:00	2011-10-29 23:59:00
2011	2	2011-10-30 00:00:00	2011-11-26 23:59:00
2011	3	2011-11-27 00:00:00	2011-12-31 23:59:00
2011	4	2012-01-01 00:00:00	2012-01-28 23:59:00
2011	5	2012-01-29 00:00:00	2012-02-25 23:59:00
2011	6	2012-02-26 00:00:00	2012-03-31 23:59:00
2011	7	2012-04-01 00:00:00	2012-04-28 23:59:00
2011	8	2012-04-29 00:00:00	2012-06-02 23:59:00
2011	9	2012-06-03 00:00:00	2012-06-30 23:59:00
2011	10	2012-07-01 00:00:00	2012-07-28 23:59:00
2011	11	2012-07-29 00:00:00	2012-09-01 23:59:00
2011	12	2012-09-02 00:00:00	2012-09-29 23:59:00
2012	1	2012-09-30 00:00:00	2012-10-27 23:59:00
2012	2	2012-10-28 00:00:00	2012-12-01 23:59:00
2012	3	2012-12-02 00:00:00	2012-12-29 23:59:00
2012	4	2012-12-30 00:00:00	2013-02-02 23:59:00
2012	5	2013-02-03 00:00:00	2013-03-02 23:59:00
2012	6	2013-03-03 00:00:00	2013-03-30 23:59:00
2012	7	2013-03-31 00:00:00	2013-04-27 23:59:00
2012	8	2013-04-28 00:00:00	2013-06-01 23:59:00
2012	9	2013-06-02 00:00:00	2013-06-29 23:59:00
2012	10	2013-06-30 00:00:00	2013-07-27 23:59:00
2012	11	2013-07-28 00:00:00	2013-08-31 23:59:00
2012	12	2013-09-01 00:00:00	2013-09-28 23:59:00

I have manage to link individual other tables with transaction date using intervalmatch, however this is creating a very large dataset. Was wondering if anyone had any better solutions for this. Examples would be a sales order that has the creation date, due date is linke linked to an Invoice with a creation and due date of its own.

Any help clues or advice would be greatly appreciated.

Re: Fiscal Periods

Fri, 20 Sep 2013 20:28:58 GMT

Here is a code snip of how I do it.

1. Read your table and get the low date and high date

2. Create a table with dates from low to high

3.Intercal match dates to period

Clean up interal match tavles to create one table

LOAD
    textbetween([Fiscal year/period - Fiscal year/period Level 01 (Key)],'.[DT',']') as DTYearPeriod,
     [Fiscal year/period - Fiscal year/period Level 01 (Text)] as Period_Year_Text,
     "Fiscal year/period - Fiscal year/period Level 01 - [10FISCPER]",
    "Fiscal year/period - Fiscal year/period Level 01 - [20FISCPER]",
     "Fiscal year/period - Fiscal year/period Level 01 - [50FISCPER]",
     num#(right("Fiscal year/period - Fiscal year/period Level 01 - [20FISCYEAR]",4)) as Fiscal_Year,
     "Fiscal year/period - Fiscal year/period Level 01 - [40FISCYEAR]" as FYYearText,
     "Fiscal year/period - Fiscal year/period Level 01 - [50FISCYEAR]",
     "Fiscal year/period - Fiscal year/period Level 01 - [80FISCPER]",
     "Fiscal year/period - Fiscal year/period Level 01 - [20NUMDAY]" as Days_in_Period,
     "Fiscal year/period - Fiscal year/period Level 01 - [20NUMWDAY]",
     "Fiscal year/period - Fiscal year/period Level 01 - [20FISCPER3]" as Period_Number,
     date(date#("Fiscal year/period - Fiscal year/period Level 01 - [20DATEFROM]",'DD.MM.YYYY'),'MM/DD/YYYY') as Period_Start_Date,
     date(date#("Fiscal year/period - Fiscal year/period Level 01 - [20DATETO]",'DD.MM.YYYY'),'MM/DD/YYYY') as Period_End_Date,
     date(date#("Fiscal year/period - Fiscal year/period Level 01 - [20DATEFROM]",'DD.MM.YYYY'),'MM/DD/YYYY')&'|'&date(date#("Fiscal year/period - Fiscal year/period Level 01 - [20DATETO]",'DD.MM.YYYY'),'MM/DD/YYYY') as Period_Key

FROM FISCPER.QVD (qvd);

Low_Date_Table:
Load
Min(Period_Start_Date) as Low_Date
Resident Fisc_Per;
High_Date_Table:
Load
   max(Period_Start_Date) as High_Date
Resident Fisc_Per;
Let varMinDate = Num(Peek('Low_Date', 0, 'Low_Date_Table'));
Let varMaxDate = Num(Peek('High_Date', 0, 'High_Date_Table'));
DROP Tables Low_Date_Table,High_Date_Table;

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

Dates:
Load Date
Resident TempCalendar;
drop table TempCalendar;

TempCalendar:
Load
Period_Start_Date,
Period_End_Date
Resident Fisc_Per;

IntervalMatch:
IntervalMatch (Date)
Load distinct Period_Start_Date, Period_End_Date resident TempCalendar;

Drop Tables Dates ,TempCalendar;

Dim_Fiscal_Year_Calendar:
Load
Period_Start_Date&'|'&Period_End_Date as Period_Key,
Date
Resident IntervalMatch;
left join
load
*
Resident Fisc_Per;
drop Tables IntervalMatch,Fisc_Per;

drop Field Period_Key;

Store * from Dim_Fiscal_Year_Calendar into ..\Operational QVDs\Master Data\Dim_Fiscal_Year_Calendar.qvd(qvd);

Re: Fiscal Periods

Thu, 02 Jan 2014 10:47:51 GMT

Hi Shawn,

I'm trying to follow this through but am struggling. Do I not need to have FROM FISCPER.QVD (qvd); to understand fully? If so can you post that as well?

Thanks
to