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
