Dates

[tmumaw]

I have 2 tables one table contains a transaction date and the other contains a plan date. I also have a Date table which contains (actual date, Fiscal Year, Fiscal Period, Fiscal Week, and Week Ending date). I need to be able to look at my transaction date and plan date in one pivot table. The user needs the ablility to select a Fiscal Year, Fiscal Period or a Fiscal Week. How can I tie all these dates together?

Thanks

ZTRPST:

LOAD %ContractItemKey,
Status,
Date,
sum(Hours) as [Hrs Worked]
FROM (qvd)
group by %ContractItemKey, Status, Date;

Plan:

LOAD autonumber([Profit Center] & '-' & [%MaterialGroup_Key]) as PlanKey,
// [Profit Center],
// %MaterialGroup_Key,
Date as [Plan Date],
Plan
FROM (ansi, txt, delimiter is ',', embedded labels, msq);

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date] as Date,
WEDAT,
WKNO,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)', 1) AS MTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag
FROM (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

[Miguel_Angel_Baeyens]

Hello Thom,

I would use a cyclic group containing some time related dimensions (month, year, quarter..) but above all, I recommend you to build a master calendar. Several threads in this forum discuss that, but you can take a look at this. Depending on your data source schema, you may use more than one master calendar (i.e.: one for Sales, another for Accounts).

[Oleg_Troyansky]

Thom,

generally speaking, if you want to compare "plan to actual", you need to find a way of keeping the same Date field and not two separate fields. I know, many times it's challenging and leads to synthetic keys or loops. However, there are techniques of dealing with that. Two most commonly used are "building link tables" or "concatenating all the data into one "Fact" table". Both techniques have been widely discussed in this forum. BOth techniques are also described in QlikView standard "Developer II" class.

cheers,