Single Fact Table for Fiscal Year Master Calendar

Fri, 06 Mar 2015 17:22:31 GMT

Hi All,

I have read through many links on how to create a Master Calendar and Single Fact Table. I have tried different options and so far things are not working. What I am trying to accomplish is Fiscal Year Master Calendar using two different date fields. The field names are RequestDate and StartDate. They are in two different tables. I have copied the script below and any assistance would be helpful:

//Energy Data Table//

UDEnergyBillData:

LOAD DudeAccountNumber,

Building,

Account,

AccountProvider,

AccountUtilityType,

AccountUOM,

Period,

Use,

Amount,

StartDate,

EndDate;

SQL SELECT *

FROM UDEnergyBillData;

//Work Order Data Table//

MDWorkOrder:

LOAD DudeAccountNumber,

WorkOrderNumber,

Status,

Priority,

RequestDate,

LocationName,

BuildingName,

ActualCosts,

ActualHours,

Purpose,

Craft,

Project,

Classification,

Type,

PMScheduleID;

SQL SELECT *

MDWorkOrder;

//Fact Table to Combine dates into one table//

FactTable:

Load DudeAccountNumber,

Makedate(year(StartDate),month(StartDate),day(StartDate)) as Date,

'Start' as Link

Resident UDEnergyBillData;

Concatenate(FactTable)

Load DudeAccountNumber,

Makedate(year(RequestDate),month(RequestDate),day(RequestDate)) as Date,

'Request' as Link

Resident MDWorkOrder;

//Fiscal Year Master Calendar//

Set vFM = 7 ;	// First month of fiscal year
Set vFD = 6;	// First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load distinct

Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year

Dual(Month, fMonth)

as FMonth, // Dual fiscal month

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Ceil((Date-StartOfFWeekOne+1)/7) as FWeekNo,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1

as fMonth, // Numeric fiscal month

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

WeekStart(Date,0,$(vFD))

as StartOfFWeekOne,

Load

Date,

YearStart(Date,0,$(vFM)) as FYearStart,

Year(Date)	as Year,
Month(Date)	as Month,

Date(Monthstart(Date), 'MMM-YYYY') as MonthYear,

Week(Date)

as ISOWeekNo,

Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFD)),7)+1) as WeekDay,

Day(Date)	as Day
// Date(BillDate'MM/DD')	as DATEMMDD

Resident FactTable;

Exit Script;

I do not get any error messages or synthetic keys but when I create list box on my MainDashboard page the date fields dont change the data and then month list box is not in Fiscal year order. The Master Calendar code I am using above seems to work if I have one date field so I am thinking it is with my Fact Table logic. Let me know if there is anything else you need.

Thanks in advance

David

Re: Single Fact Table for Fiscal Year Master Calendar

Gysbert_Wassenaar — Fri, 06 Mar 2015 17:47:01 GMT

Can you post an example qlikview document that demonstrates the problem?

Re: Single Fact Table for Fiscal Year Master Calendar

Sun, 08 Mar 2015 02:34:39 GMT

I figured it out.

Re: Single Fact Table for Fiscal Year Master Calendar

Tue, 24 Nov 2015 16:58:33 GMT

Hi David,

would you be so kind and post the changes in text form as I can't open the .qvw file on my desktop? That would be great! Thanks.

topic Re: Single Fact Table for Fiscal Year Master Calendar in QlikView

Single Fact Table for Fiscal Year Master Calendar

Re: Single Fact Table for Fiscal Year Master Calendar

Re: Single Fact Table for Fiscal Year Master Calendar

Re: Single Fact Table for Fiscal Year Master Calendar