Help with diagram calculating open tickets/month

[#1]

Hello

Need help with a diagram presentation that i struggle with.

I have error tickets with ID, created date and (if closed) also a closed date.

I want to show created sum tickets/month, closed/month and open (sum created- sum closed) tickets per moth.

For example 2011-01-01

Created: 2

Closed: 1

Open: (2-1) 1

ID	Created	Closed
15 629 469	2011-01-01	2011-01-01
15 644 230	2011-01-01	2011-02-01
15 664 518	2011-02-01	2011-02-01
15 672 243	2011-02-01	2011-03-01
15 673 160	2011-02-01	2011-02-01
15 675 024	2011-02-01	2011-02-01
15 692 713	2011-02-01	2011-02-01
15 693 144	2011-02-01	2011-05-01
15 694 301	2011-02-01	2011-07-01
15 708 302	2011-02-01	2011-02-01
15 747 484	2011-02-01	2011-03-01
15 747 559	2011-02-01	2011-03-01
15 748 161	2011-02-01	2011-09-01

[AbhijitBansode]

Use belwo technique at script level:

 

LOAD
*,
if(Closed_Month>Created_Month,1,0) as counter;
LOAD *,
Num(Month(Created)) as Created_Month,
Num(Month(Closed)) as Closed_Month;
LOAD * Inline
[
ID, Created, Closed
15629469, 2011-01-01, 2011-01-01
15644230, 2011-01-01, 2011-02-01
15664518, 2011-02-01, 2011-02-01
15672243, 2011-02-01, 2011-03-01
15673160, 2011-02-01, 2011-02-01
15675024, 2011-02-01, 2011-02-01
15692713, 2011-02-01, 2011-02-01
15693144, 2011-02-01, 2011-05-01
15694301, 2011-02-01, 2011-07-01
15708302, 2011-02-01, 2011-02-01
15747484, 2011-02-01, 2011-03-01
15747559, 2011-02-01, 2011-03-01
15748161, 2011-02-01, 2011-09-01
];

Now at front end, you need to write expression as Sum (counter) to get no. of open tickets with Created as a dimension.

Hope this is what you are looking for.

[#3]

This gives me number of created tickets per month. Not how many tickets that were open last year etc.

[manas_bn]

Hello,

You need to create a table structure like this:

ID

Date // Common for Created/Closed

Activity // Closed or Open

Open_Flag

Closed_Flag

So if you have to see how many were open or closed on a particular date, all you have to do is sum up the open and closed flags. Or count the Activity where it's 'Open'. Unfortunately, I am not able to attach an example, but below is the code and an image which might help...

// Raw table

Temp:

Load * inline [

ID,StartDate,EndDate

1,1/1/2013,1/1/2013

2,1/1/2013,1/2/2013

3, 3/1/2013,3/2/2013

4,4/1/2013,5/2/2013

5, 4/1/2013,

];

// Create a new table for Open dates

TicketInfo:

Load ID, StartDate as Date, 'Open' as Event, 1 as Open_Flag Resident Temp;

// Concatenate Closed Date info

Concatenate(TicketInfo)

Load ID, EndDate as Date, 'Closed' as Event,

// If EndDate is blank, Closed Flag = 0

if(trim(EndDate)='',0,1) as Closed_Flag

Resident Temp;

// Clean up

drop table Temp;

Hope this helps.

Manas