topic Re: Multiple dates (master calendar in QlikView

Multiple dates (master calendar

Mon, 09 Mar 2015 03:53:05 GMT

I created 4 master calendars for the following dates

[Fin_Month]

[Snapshot Month]

[Cust_Month]

The result I wanted to get is that when i select the snapshot month = Jan 31 2015.. it will automatically select Fin_Month = Jan 31 2015 and Cust_Month - Jan 31 2015 as well

this is how I load Snapshot Month

headcount:

LOAD
Date(Alt(Date#([Snapshot Month], 'YYYY/MM/DD'),
Date#([Snapshot Month], 'DD/MM/YYYY'),
Date#([Snapshot Month], 'DD/MM/YY'),
Date#([Snapshot Month], 'MM/DD/YYYY'),
Date#([Snapshot Month], 'M/D/YYYY'),
),'MMM-YY') as [Snapshot Month],

my master calendars for the 3 respective dates:

FinCalendar:

Load
Week(Fin_Month) as FinWeek,
Year(Fin_Month) as FinYear,
Month(Fin_Month) as FinMonth,
Date(Fin_Month) as FinDate
Resident actualbudget;

BISCalendar:
Load
Week([Snapshot Month]) as BISWeek,
Year([Snapshot Month]) as BISYear,
Month([Snapshot Month]) as BISMonth,
Date([Snapshot Month]) as BISDate
Resident headcount;

CusCalendar:
Load
Week(Cust_Month) as CusWeek,
Year(Cust_Month) as CusYear,
Month(Cust_Month) as CusMonth,
Date(Cust_Month) as CustDate
Resident custdashboard_actuals;

which I loaded one at a time (so FinCalendar first.. load.. then BIS calendar .. load.. then CusCalendar load)

what am I missing? Note: am new to Qlikview

Re: Multiple dates (master calendar

Mon, 09 Mar 2015 03:55:03 GMT

Snapshot Month	BISDate	FinDate	CustDate
Jan-15	1/31/2015	12/31/2014	12/31/2014
Jan-15	1/31/2015	12/31/2014	1/31/2015
Jan-15	1/31/2015	1/31/2015	12/31/2014
Jan-15	1/31/2015	1/31/2015	1/31/2015

resulting dates above.. which is not what I want.. this is what I want

Snapshot Month	BISDate	FinDate	CustDate
Jan-15	1/31/2015	1/31/2015	1/31/2015
Dec-14	12/31/2014	12/31/2014	12/31/2014

Re: Multiple dates (master calendar

chrislofthouse — Mon, 09 Mar 2015 08:45:38 GMT

You would need to join them on a common field i.e. [Snapshot Month], however you must be careful not to introduce joins into your model. Without seeing your application first hand I would guess at this:

headcount:

LOAD
Date(Alt(Date#([Snapshot Month], 'YYYY/MM/DD'),
Date#([Snapshot Month], 'DD/MM/YYYY'),
Date#([Snapshot Month], 'DD/MM/YY'),
Date#([Snapshot Month], 'MM/DD/YYYY'),
Date#([Snapshot Month], 'M/D/YYYY'),
),'MMM-YY') as [Snapshot Month],

my master calendars for the 3 respective dates:

FinCalendar:

Load
Week(Fin_Month) as FinWeek,
Year(Fin_Month) as FinYear,
Month(Fin_Month) as FinMonth,
Date(Fin_Month) as FinDate,
Date(Fin_Month,'MMM-YY') as [Snapshot Month] //Added Join
Resident actualbudget;

BISCalendar:
Load
Week([Snapshot Month]) as BISWeek,
Year([Snapshot Month]) as BISYear,
Month([Snapshot Month]) as BISMonth,
Date([Snapshot Month]) as BISDate,
Date([Snapshot Month],'MMM-YY') as [Snapshot Month] //Added Join
Resident headcount;

CusCalendar:
Load
Week(Cust_Month) as CusWeek,
Year(Cust_Month) as CusYear,
Month(Cust_Month) as CusMonth,
Date(Cust_Month) as CustDate,
Date(Cust_Month,'MMM-YY') as [Snapshot Month] //Added Join
Resident custdashboard_actuals;

Another way to do this is in the dashboard itself, creating an 'artificial' link, using Set Analysis or triggers.

Re: Multiple dates (master calendar

Mon, 09 Mar 2015 09:05:27 GMT

Hi Lanie,

I Agree ChrisLofthouse answer. At least all three need to have one common filed to get your desired out put.

Re: Multiple dates (master calendar

Mon, 09 Mar 2015 09:57:22 GMT

hi christopher i followed the above..

am still getting this

Fin_Month	Snapshot Month
12/31/2014	Dec-14
12/31/2014	Jan-15
1/31/2015	Dec-14
1/31/2015	Jan-15

Re: Multiple dates (master calendar

chrislofthouse — Mon, 09 Mar 2015 10:02:41 GMT

Can you upload the application and I can take a look?

Re: Multiple dates (master calendar

Mon, 09 Mar 2015 10:27:06 GMT

how do I instert the qvw here? sorry

SET

ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

headcount:
LOAD
Date(Alt(Date#([Snapshot Month], 'YYYY/MM/DD'),
Date#([Snapshot Month], 'DD/MM/YYYY'),
Date#([Snapshot Month], 'DD/MM/YY'),
Date#([Snapshot Month], 'MM/DD/YYYY'),
Date#([Snapshot Month], 'M/D/YYYY'),
),'MMM-YY') as [Snapshot Month],
[Business Area],
// Functions,
Manco,
LT,
[Employee ID],
[Preferred Forename],
Surname,
[Work Email Address],
[Corporate Title],
[Gender Code],
[Cost Centre],
[Line Manager ID],
[Line Manager Surname],
[Line Manager First Name],
[Manager Level],
[Business Title],
[Accredited Service Date],
[Continuous Service Band],
[Work Location - Country],
[Permanent / FTC],
Count
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Headcount_.xls]
(biff, embedded labels, table is headcount$);

perspectives:
LOAD Perspective_ID,
Perspective,
KPI_ID,
Initiative,
Script,
Target,
Theme_Des,
Phase,
T_Green,
T_Amber,
T_Red
FROM
[\\sinfiler2\Asia_Operations\Dashboard\\PBMScorecard\DataFiles\excel\Dashboard Priority.xlsx]
(ooxml, embedded labels, table is KPI);

customer:

LOAD Cust_Month,
// Theme,
// Team,
// Product,
Definition,
[Current Month],
[Previous Month],
Comments as Cust_comments,
Tgt
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Customer_dashboard.xls]
(biff, embedded labels, table is actuals$);
LOAD Initiative,
//[Business Area],
LT,
[Theme Team],
Product,
KPI,
Definition,
Targets,
Target_Green,
Target_Amber,
Target_Red
FROM
[\\sinfiler2\Asia_Operations\Dashboard\\PBMScorecard\DataFiles\excel\Customer_dashboard.xls]
(biff, embedded labels, table is targets$);

actualbudget:
LOAD Fin_Month,
//Manco,
LT,
[Budget Owner],
[Khalix Node],
[Division],
[Finance Division],
CATM,
CALM,
[MoM Variance],
CBYTD_TM,
CAYTD_TM,
VABYTD,
VABYTD_P,
CFY_Budget,
CFY_Forecast,
VBFFY,
VBFFY_P
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Finance-breakdown.xlsx]
(ooxml, embedded labels, table is Cost_ALL);

LOAD// LT,
[Khalix Node],
// [Finance Division],
HCA_lastmonth,
HCA_thismont,
//F5,
HCB_thismonth,
HCYTD_ThisMonth,
HCYTD_Budget,
HCFull_budget,
HCYTD_Forecast
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Finance-breakdown.xlsx]
(ooxml, embedded labels, table is HC_ALL);

openroles:
LOAD [Aging Actions],
[Aging since Final Approval],
[Role #],
[Role Description / Job Title],
[Line Managers],
[Role Status],
Functions,
[Budget type],
Type,
[Final Approved Date],
[Cost Centre],
// Country,
// [Corporate Title],
// Comments,
[To be filled]
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\openroles_massaged.xls]
(biff, embedded labels, table is Sheet1$);

JML_combined:
LOAD// [Business Area],
Drivers,
// Manco,
// Functions,
LT,
JML_country,
[Employee ID] as JML_EID,
Forename,
Surname as JML_surname,
JML_Effectivedate,
JML_Month,
Voluntary,
[High Performer Flag],
[STT Leaver FTE],
[SubDivision 1 - Moved From],
[SubDivision 2 - Moved From],
[SubDivision 3 - Moved From],
[Old Work Location - Location Code],
[Old Work Location - Country],
ct
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\JML_combined.xls]
(biff, embedded labels, table is JML_combined$);

perspectives:
LOAD Perspective,
Category,
[Employee FTE]
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Perspectives.xlsx]
(ooxml, embedded labels, table is Sheet1);

eperf:
LOAD [Eperf Programme],
[eperf Milestone] as eperf_milestone,
[eperf Capture] as eperf_capture,
[eperf Milestone Deadline],
// Drivers,
// [Business Area],
//Functions,
//[Work Location - Country],
[Employee ID],
//[Employee Name],
//[Preferred Forename],
//Surname,
//"SUM([# Objective Settings Not Started]",
//"SUM([Objective Settings In Progress (with Employee)]",
//"SUM([Objective Settings In Progress (with Manager)])",
//"SUM([# Objective Settings Completed])",
//HC,
[% Completion] as eperf_completion,
"=IF([% Completion]<<1,'no action reqd','investigate')" as eperf_action
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\eperf_action.xls]
(biff, embedded labels, table is eperf_aciton$);

training:
LOAD [Employee ID],
[Employment Status],
[Completion Status],
[Employee Individual Title],
[First Name],
[Last Name],
[Email Address],
[Learning Course],
[Learning Course Name],
[Learning Activity],
[Delivery Method Name],
[Activity Start Date],
[Completion Date],
[Manager Details],
// Division,
// [Sub Division 1],
// [Sub Division 2],
// [Sub Division 3],
// [Sub Division 4],
// [Sub Division 5],
// [Sub Division 6],
// [Sub Division 7],
// [Cost Centre],
[Activity End Date],
[Original Hire Date],
Year_Complete
FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\training.xlsx]
(ooxml, embedded labels, table is [2014_training]);

weekly_outstanding:
LOAD
//[Snapshot Month],
[Created Date (Issue)],
[Business Area],
[Organisation Level 6 (Issue)],
[Organisation Level 7 (Issue)],
[Organisation Name (Issue)],
[Location Name (Issue)],
Countid,
[Issue ID],
[Source Reference],
[Issue Type],
[Issue Name],
[Detailed Description (Issue)],
[Impact Rating],
[Issue Status],
flgClosedPendReview,
[Issue Owner Name (Issue)],
[Issue Workflow Owner],
[Last Updated Date (Issue)],
[Accepted Date (Issue)],
[Issue Due],
[Target Completion Date (Issue)],
[Revised Target Completion Date (Issue)],
LastComments,
LAST_UPDATE_USER_RK,
[Catalogue Risk Level 4 (Issue)],
Policy,
[Catalogue Risk Level 5 (Issue)],
[Catalogue Risk Level 6 (Issue)],
DateClosedPendReviewToYes,
[Organisation Level 5 (Issue)],
[Action ID],
[Action Description],
[Action Status],
[Action Owner],
[Current Target Completion Date (Action)],
[Action Dependency],
[Closure Date (Action)],
[Month (Issues)],
[Issues Due Band],
[Month (Action)],
[Action Due],
[Age (PD Action)],
[Action Age Band],
[Date to CPR (Y)],
[CPR Ages]

FROM
[\\sinfiler2\Asia_Operations\Dashboard\PBMScorecard\DataFiles\excel\Weekly_Outstanding_R!.xls]
(biff, embedded labels, table is Data$);

FinCalendar:

Load Distinct
Week(Fin_Month) as FinWeek,
Year(Fin_Month) as FinYear,
Month(Fin_Month) as FinMonth,
Date(Fin_Month) as FinDate,
Date(Fin_Month,'MMM-YY') as [Snapshot Month] //Added Join
Resident actualbudget;
BISCalendar:
Load Distinct
Week([Snapshot Month]) as BISWeek,
Year([Snapshot Month]) as BISYear,
Month([Snapshot Month]) as BISMonth,
Date([Snapshot Month]) as BISDate,
Date([Snapshot Month],'MMM-YY') as [Snapshot Month] //Added Join
Resident headcount;

CustCalendar:
Load Distinct
Week (Cust_Month) as CustWeek,
Year(Cust_Month) as CustYear,
Month(Cust_Month) as CustMonth,
Date(Cust_Month) as CusDate,
Date(Cust_Month,'MMM-YY') as [Snapshot Month]
Resident customer;

LinkTable:
Load Distinct
[Snapshot Month]
Resident FinCalendar;

Load Distinct
[Snapshot Month]
Resident BISCalendar;
Load Distinct
[Snapshot Month]
Resident CustCalendar;

MasterCalendar:
LOAD
DATE([Snapshot Month],'YYYYMMDD') AS [Snapshot Month],

DATE([Snapshot Month],'YYYYMM') AS ID_TIME_YEARMONTH,

DATE([Snapshot Month],'MMM-YY') AS ID_TIME_YEARMONTHDESC,

DATE([Snapshot Month],'YYYYMMDD') AS ID_TIME_YEARMONTHDAY,

'Q'&ceil(Month([Snapshot Month])/3) AS ID_TIME_QUARTER,

YEAR([Snapshot Month]) AS ID_TIME_YEAR,

MONTH([Snapshot Month]) AS ID_TIME_MONTH,

NUM(MONTH([Snapshot Month])) AS ID_TIME_MONTH#,

NUM(DAY([Snapshot Month])) AS ID_TIME_DAY,

WEEKDAY([Snapshot Month]) AS ID_TIME_DAY_WEEK,

NUM([Snapshot Month]) AS ID_TIME_DATE#

Resident LinkTable;

Re: Multiple dates (master calendar

Mon, 09 Mar 2015 15:02:11 GMT

@as attached pls help

Re: Multiple dates (master calendar

Roop — Mon, 09 Mar 2015 15:45:57 GMT

This solution may work but your month format is different between data sources! If you have a look at your LinkTable you have different month format types so this will not create the correct selection at all.

Please browse your LinkTable and you will see the main issue

Hope this helps

Rupert