Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
This gives me number of created tickets per month. Not how many tickets that were open last year etc.
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