Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table like below:
I am auto generating dates from 1-jan-2014 till today().
Trying to merge this dates with the [Date opened] and [Date closed]
I want to get an output table something like below:
I am not able to get April entry which has closed ticket.
Expression of opened tickets = count([Date opened])
Expression of closed tickets = count([Date closed])
Please do let me know if additional infor required.
Regards
Ren
Use interval Match function
Use below script
=================
T1:
Load
ID,
Status,
Date(Date#([Date opened],'DD-MMM-YYYY')) as [Date Opened],
Date(Date#([Date closed],'DD-MMM-YYYY')) as [Date Closed]
Inline
[
ID, Status, Date opened, Date closed
8000003001, Closed, 05-Mar-2014, 01-Apr-2014
8000003102, In Process, 17-Mar-2014, -
8000003122, In Process, 18-Mar-2014, -
8000003121, In Process, 18-Mar-2014, -
8000003204, In Process, 26-Mar-2014, -
8000003203, Proposed Solution, 26-Mar-2014, -
];
Opened:
Mapping Load
[Date Opened]
Resident T1;
Closed:
[Date Closed]
NoConcatenate
Final:
ApplyMap('Opened',ID,Null()) as Date,
'Open' as DataType
ApplyMap('Closed',ID,Null()) as Date,
'Close' as DataType
===========================================
Now create a straight table
Dimension
Date
and Tick Suppress When Value is NULL
Expressions
Open Tickets
Count({<DataType = {'Open'}>}ID)
Close Tickets
Count({<DataType = {'Close'}>}ID)
===============
File enclosed for your reference.
Try to load your table like
LOAD 'Open Tickets' as TicketFlag,ID, Status,[Date opened] as Date;
LOAD * INLINE [
ID, Status, Date opened
8000003001, Closed, 05-Mar-2014
8000003102, In Process, 17-Mar-2014
8000003122, In Process, 18-Mar-2014
8000003121, In Process, 18-Mar-2014
8000003204, In Process, 26-Mar-2014
8000003203, Proposed Solution, 26-Mar-2014
Concatenate
LOAD 'Closed Tickets' as TicketFlag,ID, Status,[Date closed] as Date;
ID, Status, Date closed
8000003001, Closed, 01-Apr-2014
8000003102, In Process, -
8000003122, In Process, -
8000003121, In Process, -
8000003204, In Process, -
8000003203, Proposed Solution, -
And in front end take a straight table
Dimension >> =if(Len(Date)>1,Date)
Expression >> Count({<TicketFlag={'Open Tickets'}>} ID)
Expression >> Count({<TicketFlag={'Closed Tickets'}>} ID)
And connect this table with Master Calendar
Let varMinDate = Num(Makedate(2014,1,1));
Let varMaxDate = Num(Makedate(Year(today()),Month(today()),Day(today())));
Datefield:
LOAD
date($(varMinDate)+IterNo()-1) AS Datefield,
date($(varMinDate)+IterNo()-1,'DD-MMM-YYYY' ) AS Date
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
After loading table and when creating straight table chart, if in date column null values shown then use dimension like
Take a straight table
Dimension >> =if(Len(Date)>1,Date) and Suppress when value null
'Opened' as OPEN_CLOSED_FLAG,
DateOpened,
DateOpened as %CommonCalendarLink,
DateClosed as DateClosed_Ref
resident orginal;
concatenate
'Closed' as OPEN_CLOSED_FLAG,
DateClosed,
DateClosed as %CommonCalendarLink
resident Orginal;
drop table Orginal;
---> Now connect the Master Calendar with %CommonCalendarLink
in front end take Date field of Master calendar
and expression :
For Opened :
Count({<OPEN_CLOSED_FLAG={'Opened'}>}DateOpened)
For Closed :
Count({<OPEN_CLOSED_FLAG={'Closed'}>}DateClosed)
