Two Dates One Table

[MikeD1]

Hi,

I have a table with two different date fields. Ticket_Created and Ticket_resolved.

How can I display the sum per Year/Month of created and resolved? I've tried to use a Master Calendar but I have many errors during field date definition.

Could anyone help me?

Thanks in advance.

[rafaelsferreira]

Hi,

You can use a table that concatenates the two dates and create a link between your original table and the calendar_table.

Example:

ORIGINAL_TABLE with key, Ticket_Created and Ticket_resolved.

BRIDGE_TABLE:
Load
key,
Ticket_Created as Date,
Resident ORIGINAL_TABLE
Where not IsNull(Ticket_Created); //Optional

Concatenate(BRIDGE_TABLE)
Load
key,
Ticket_resolved as Date,
Resident ORIGINAL_TABLE
Where not IsNull(Ticket_resolved); //Optional

CALENDAR_TABLE:
Load
Date,
Day(Date) as [Day],
Dual(Capitalize(Month(Date)), Num(Month(Date))) as [Month],
Year(Date) as [Year];
Load Distinct
Date
Resident BRIDGE_TABLE;

[MikeD1]

Thank you for the replaty.

First problem: if I unlock the section I cannot add , in the future , others table right?

So maybe I have to complete the table inserting and configuration BEFORE unlock correct?

[MikeD1]

Second problem: this is the structure:

[jiraissue]:
SQL SELECT "ID",
"pkey",
"issuenum",
"PROJECT",
"REPORTER",
"ASSIGNEE",
"CREATOR",
"issuetype",
"SUMMARY",
"DESCRIPTION",
"ENVIRONMENT",
"PRIORITY",
"RESOLUTION",
"issuestatus",
"CREATED",
"UPDATED",
"DUEDATE",
"RESOLUTIONDATE",
"VOTES",
"WATCHES",
"TIMEORIGINALESTIMATE",
"TIMEESTIMATE",
"TIMESPENT",
"WORKFLOW_ID",
"SECURITY",
"FIXFOR",
"COMPONENT",
"ARCHIVEDBY",
"ARCHIVEDDATE",
"ARCHIVED"
FROM "JIRA"."dbo"."jiraissue";

BRIDGE_TABLE:
Load
jiraissue.id, ****error not found *****even if I use jiraissue.id or id********
created as Date
Resident jiraissue;

Concatenate(BRIDGE_TABLE)
Load
***here I obtain error with jiraissue.id or id************
resolutiondate as Date
Resident jiraissue;

[rafaelsferreira]

I think you have to write the LOAD script with the id field like this "ID".

There is no need of table name before the field name.

[MikeD1]

Thanks @rafaelsferreira 

is key sensitive.

Now, how I can use all of this in one chart ( bar ) to display the total amount of ticket opened and closed in a specific month/year?

I try to add Month of Calendar_Date as dimension but how I can count the ticket with the date as created and in the other bar the ticket with the date as closed?

[rafaelsferreira]

Ok.

You can add a column "DateType" to the bridge table:

BRIDGE_TABLE:
Load
key,
Ticket_Created as Date,

'TICKET_CREATED' as DateType

Resident ORIGINAL_TABLE
Where not IsNull(Ticket_Created); //Optional

Concatenate(BRIDGE_TABLE)
Load
key,
Ticket_resolved as Date,

'TICKET_RESOLVED' as DateType

Resident ORIGINAL_TABLE
Where not IsNull(Ticket_resolved); //Optional

You can create measures filtering by DateType with Set Analysis.

[MikeD]

Hi @rafaelsferreira 

I've create this structure:

BRIDGE_TABLE:
Load
'CREATED' as Flag_Date
,ID
,Date(CREATED) as Temp_Date
Resident jiraissue
Where not IsNull(CREATED); //Optional;

Concatenate(BRIDGE_TABLE)
Load
'RESOLUTIONDATE' as Flag_Date
,ID
,Date(RESOLUTIONDATE) as Temp_Date
Resident jiraissue
Where not IsNull(RESOLUTIONDATE);

MasterCalendar:
Load
Date(Date) as Temp_Date
,Year(Date) as Year
,'Q' & ceil(Month(Date)/3) as Quarter
,Month(Date) as Month
,Week(Date) as Week
,Dual(Year(Date)&'-'&Month(Date), monthstart(Date)) as MonthYear
,Weekday(Date) as WeekDay
;
Load
MinDate+Iterno() - 1 as Date
While MinDate + IterNo() - 1 <=MaxDate;
LOAD
Min(FieldValue('Temp_Date',RecNo()))-1 as MinDate
,Max(FieldValue('Temp_Date',RecNo())) as MaxDate
AUTOGENERATE FieldValueCount('Temp_Date');

Unfortunately if I choose MonthYear in the MasterCalendar as dimension for a table no record seems to be loaded.

What's wrong?

[MikeD]

Eureka!!!!!

I did it ! Thanks again.

Another question: now I have unlock the section but how can I add more table and link together?

[rafaelsferreira]

Hi, you can use LOAD scripts for new tables after unlock.