Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Associating Multiple Date Fields to a single fiscal calendar

I have a calendar table that with gregorian date as the "key" and Fiscal Month, Year, Week as fields.  I have an app that allows me to define a fiscal calendar for different clients; e.g. 4-4-5 quarters with every period starting on a Sunday - for example 2011 starting Jan 2, 2011.  Periodically you have to add a 53 week to shift the calendar a bit.  So given any gregorian date, I can tell you exactly what is the fiscal time.  If I had only one date in the field list it's easy just Select FiscalPeriod,FiscalYear,GregorianDate as 'OrderDate'  from dimTime to associate it with Select OrderNbr,OrderDate from Orders.    Of course their is never one date in a table; order date, requested date, promise date, revised promise date, maybe you'll get it on this date, etc.  I want to be able to summarize data by FiscalPeriod for multiple date fields.

Loading the calendar mutiple times will not work.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

If I understood you correctly, you probably need a linkage table.

There are some threads here explaining that concept with regards to a master calendar and multiple date fields, like

http://community.qlik.com/message/94377

http://community.qlik.com/message/144958

or search for linkage table in the forum.

Hope this helps,

Stefan

View solution in original post

2 Replies
swuehl
MVP
MVP

If I understood you correctly, you probably need a linkage table.

There are some threads here explaining that concept with regards to a master calendar and multiple date fields, like

http://community.qlik.com/message/94377

http://community.qlik.com/message/144958

or search for linkage table in the forum.

Hope this helps,

Stefan

View solution in original post

Not applicable
Author

Thanks, the latter link was the most helpful.  This is how I did it :

SQL SELECT "DAY_",

    "FISCAL_MONTH",

    "FISCAL_QTR",

    "FISCAL_WEEK_NUMBER",

    "FISCAL_YEAR",

    "WEEKDAY_NAME"

FROM contosousapp.dbo."CEP_Calendar";

SQL SELECT

    CpnyID,

    LineRef,

    OrdNbr,

    PromDate,

    QtyOrd,

    QtyShip,

    ReqDate,

    SchedRef,

    ShipDate,

    SiteID

FROM contosousapp.dbo.SOSched;

DateTable:

LOAD

CpnyID, OrdNbr, LineRef, SchedRef,PromDate as "DAY_",

'PROMDATE' as DateType

RESIDENT SOSched;

Concatenate (DateTable)

LOAD

CpnyID, OrdNbr, LineRef, SchedRef,ReqDate as "DAY_",

'REQDATE' as DateType

RESIDENT SOSched;

I build a Group Hierarchy from the fiscal calendar to allow drill down from fiscal year, to quarter, to month, and to week -- in 4-4-5 every fiscal month starts on a Sunday and ends on a Saturday and has 4 or 5 weeks.

In the table below 5/30/2011 in is Fiscal_Month 2011-006 since the Fiscal_Month 2011-005 ending 5/28/2011

CDI O0000007 00001 00001 CP0002 5/30/2011 2011-006 22
CDI O0000007 00005 00001 CP0003 5/30/2011 2011-006 22
CDI O0000007 00008 00001 CP0004 5/30/2011 2011-006 22