Associating Multiple Date Fields to a single fiscal calendar

[#1]

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.

[swuehl · Accepted solution]

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

[#3]

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