Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

A couple of Master Calendar questions:

1. I'm using data that has several date fields (created and resolved are the two I'm interested in at the moment).

I want to be able to sort by created date in any given date period, and also by reoslved date in any given period.

do I need to create a second mastercalendar with new (different) date field names for this second date?

2.. I've set up a master calendar, but when I use any mastercal fields as selection criteria all of my data ignored.

When I select the same dates using the native date field I see the correct data.

I'm sure I've made some stupid rookie mistake, but for the life of me I can't see it.

Here's my mastercal script

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q3

2, Q3

3, Q3

4, Q4

5, Q4

6, Q4

7, Q1

8, Q1

9, Q1

10, Q2

11, Q2

12, Q2

];

Temp:

Load

               min(Created) as minDate,

               max(Created) as maxDate

Resident Data;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

CMasterCalendar:

Load

               TempDate AS Created,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(TempDate) & '-' & Year(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Heres's the initial data load script.

Data:

LOAD Project,

     Key,

     Summary,

     [Issue Type],

     Status,

     Priority,

     Resolution,

     Assignee,

     Reporter,

     Creator,

     Created,

     [Last Viewed],

     Updated,

     Resolved,

     [Affects Version/s],

     [Fix Version/s],

     [Component/s],

     [Due Date],

     Watchers,

     Images,

     [Original Estimate],

     [Remaining Estimate],

     [Time Spent],

     [Work Ratio],

     [Sub-Tasks],

     [Linked Issues],

     Environment,

     Description,

     [Security Level],

     Progress,

     [Σ Progress],

     [Σ Time Spent],

     [Σ Remaining Estimate],

     [Σ Original Estimate],

     Labels,

     Flagged,

     [Time to resolution],

     [Epic/Theme],

     [Time to first response],

     [Rank (Obsolete)],

     [Customer Request Type],

     [Story Points],

     [Business Value],

     [Support Request Response],

     [Time waiting for Facilities],

     [Initial Response],

     [Time waiting for support],

     [Hold Reason],

     [Rank (Obsolete)1],

     Reviewed,

     [Time waiting for HR],

     RAG,

     [Time Waiting for IT],

     [Analysis Start Date],

     [Provide Start Time],

     [Restricted Feature],

     [Provide Estimate],

     [Reviewed By],

     [3rd Party/s],

     [Fix Available],

     [Fix Available By],

     [Database Changes],

     [Required Packages],

     [Configuration Changes],

     [Customer Peer IP Address],

     [Disc Storage],

     [IT Triage Checklist],

     [Customer Host IP address / IP Subnet / Hostnames],

     Dependencies,

     [Applicable Customers],

     [Content Team(s)],

     [Openbet Host IP address / IP Subnet / Hostnames],

     [Resolution Signoff],

     [Release Signoff],

     [CVS Build Tag],

     [Triaged By],

     Team,

     Escalated,

     [Source IP Address],

     [Incident Type],

     [Expiration Date],

     [Fix Released],

     [Negotiated Resolution],

     [Test Estimate],

     Hostname,

     [Response SLA],

     [Destination IP Address],

     Division,

     [Escalated By],

     [Problem Type],

     Escalation,

     [Service / Port],

     Branch,

     CPU,

     Memory,

     [Operating System],

     [Database Connectivity],

     [ProjectTracker URL],

     [Functional Areas],

     [Actions Required],

     [Code Reviewed By],

     [Incident Model],

     [Review Signoff By],

     [Review Signoff],

     [Game Name],

     [Last Comment By],

     [Fix Owner],

     [Released To Test By],

     [Next Step Date],

     Iteration,

     [Next Step],

     [Compliance Quote Signed Off],

     [RAIDA Type],

     [Fix Type],

     [Query Unit Test],

     [Dev Estimate],

     [Schema Review Complete],

     Account,

     [Negotiated Response],

     [Testing Resolution],

     Downtime,

     [Query Unit Test Description],

     [Timesheet Code],

     Nationality,

     Assumptions,

     [Reviewer/s],

     [Outstanding Items],

     [Passport Expiry Date],

     [Date Of Birth],

     [Security Review],

     [Performance Review Complete],

     [De-escalation],

     [Active Escalation],

     [CVS Link],

     [Hotel Information],

     [De-escalated],

     [De-escalated By],

     [Passport Number],

     [Global Rank (Obsolete)],

     [Passport Issue Date],

     [Customer Stakeholder(s)],

     [Employee Name],

     [Passport Name],

     [Last Comment Not OpenBet],

     [Preferred Arrival Airport],

     [Luggage Required],

     [Return Travel Date/Time],

     [Number Of Comments],

     [Preferred Departure Airport],

     [Desired Delivery Date],

     [Reward Details],

     [Hotel Required],

     [Fix Details],

     Bounce,

     [Rollback Plan],

     [Test Guidance],

     Impact,

     [Triage Checklist],

     [Other Info],

     [Epic Link],

     [Release Signoff By],

     [Resolution Signoff By],

     [Epic Status],

     [Initial Response (Shal)],

     [Epic Name],

     [Outbound Travel Date/Time],

     [Estimation Points],

     Rank,

     [Epic Colour],

     [Global Rank],

     [Response SLA Met],

     [Test Phase],

     Closed,

     [Post-Incident Review By],

     [Resolution SLA],

     [Closed By],

     [Resolution SLA Met],

     Sprint,

     [Post-Incident Review],

     [Upcoming Event],

     Overview,

     [Security Code Review],

     [Absence Reason],

     [Overview Completed],

     [New Address],

     [Resolved By],

     [Effective Date],

     [Absence Date],

     [New Emergency Contact Details],

     [Team (OB)],

     [Customer Reference],

     [IT Support Resolution],

     [Internal Stakeholder(s)],

     [New Contact Details],

     [New Bank Details],

     Site,

     [New Job Title],

     [New Team]

FROM

(biff, embedded labels, table is general_report$);

I've attached a copy of my data source (with most fields cut out, just leaving date, fields and a reference number field (key).

All help, advice, and "you missed this you numpty" comments welcome.

Thank you.

Rob

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

1. Yes - one solution is to use two copies of the master calendar, one linked to each date field. The alternative is to use a link table, but I think the dual master calendar is the best approach based on your post.

2. Your script looks OK to me. What may be happening is that the Created field in the calendar is not aligning with the dates in your fact table - possibly because they have a time component in them. This is what I would do in the fact load:

     LOAD

          ....

          Date(Floor(Created)) As Created,

          Time(Frac(Created)) As CreatedTime,    // need this line only if the time component is necessary in your model.

          ...

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
alexandros17
Partner - Champion III
Partner - Champion III

The answer to the first question is Yes, you have to build calendars for each date you need one.

The second problem is related to no existence of links between calendar and your source table, define in this last a field (Week, for example: weekyear(myDate)*100 + week(myDate) ... ) that link the table with the calendar.

Let me know

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

1. Yes - one solution is to use two copies of the master calendar, one linked to each date field. The alternative is to use a link table, but I think the dual master calendar is the best approach based on your post.

2. Your script looks OK to me. What may be happening is that the Created field in the calendar is not aligning with the dates in your fact table - possibly because they have a time component in them. This is what I would do in the fact load:

     LOAD

          ....

          Date(Floor(Created)) As Created,

          Time(Frac(Created)) As CreatedTime,    // need this line only if the time component is necessary in your model.

          ...

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Perfect. Thanks Jonathan.

So I would need to apply the same to my resolved field in the fact load too for my second mastercal. Thank you!

Thank you