Calculating and trending open tickets on the first of the month

[marckssg]

Hi,

I've not been able to find an answer that quite gives me what I'm looking for, but I'm sure thats me asking the search funtion the wrong question!

I'm trying to calculate the number of tickets open on the 1st of a month in a helpdesk system then trend this over 6 months in a graph.

So to identify what's open on the first of the month I've got the following formula;
IF {%Submit Date} < DateAdd('m',-1,Date(Year(CurrentDate),Month(DateAdd('m',0,CurrentDate)),1)) AND
{%Last Resolved Date} > DateAdd('m',-1,Date(Year(CurrentDate),Month(DateAdd('m',0,CurrentDate)),1)) THEN 1

I've replicated this 5 times and modified accordingly to give me the volumes for the 6 months.

Now I need to create a formula/method that will give me the first of the month that I can then drop into a graph and come up with a way of adding the 6 formulas to the graph so they plot in one line showing a trend over the 6 months.

I do have a table with just dates in and have used this in the past for "dummy" dates were dates are missing and I wanted to show a zero value instead of a missing one, but I'm having a mental block on how I'd link that table in given the date doesn't actually exisit in my main table given its what was open on this date, not opened, if that makes sense. So if I ran the report today the graph would show 1st June, 1st May, 1st April, 1st March, 1st February, 1st January.

Another thought is could I add a Union and code in there a rolling 6 months, but thats not something I've done before so wasn't sure if it would work or was the best way.

I'm using Crystal 2008, reporting off a SQL Server database, but can't do anything on the database side.

Any ideas would be most welcome.

Cheers

Marc

 

[MCuthill]

Marc,

Perhaps I am out in left field... but a question. Do you only want those Tickets opened on the 1st and resolved later trended, or is this a subset of the overall report that you are looking to trend? I *think* (not certain) you could just limit your report to those accounts opened in the given period (Jan 1 to Jun 1, in the example) with a resolved date later and count them. What I am unsure of is if your report needs the other Tickets (opened on the 2nd, 3rd, etc) on the report, or if you are only concerned with the ones for the 1st's of the months. If the latter, I *think* I have a solution (or something worth trying).

Please advise and we go from there.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[marckssg]

Hi Mike,

Yes its just a count of what was open on the first of each month, trended over 6th Months.

So for example;

Ticket 1 opened on the 10th January and closed on the 5th February I'd want to be counted as being open on the 1st February.

Ticket 2 opened on the 10th January and closed on the 5th March I'd want to be counted as being open on the 1st February and 1st March.

Be interested to hear any ideas, its not really working out with what I've tried so far!

Cheers

Marc

 

[MCuthill]

Hi Marc,

Just saw your reply here this morning. That is different again from what I had in my head (I was thinking "opened on", not being open on the first), though it definitely makes sense once I reread your original post. I am thinking it will be six conditional formulas... which I see you already have in your original post. I don't know that formulas such as this can be graphed. When I end up in situations like this, I turn the report into an "export" that can be pasted into an MS Excel file for the final presentation.

One thing to consider: I am unsure that your formula would count a ticket opened, but not yet closed?

I will ponder on it some more and advise should I come up with anything to try.

A small item, it doesn't change anything, but will cleanup your formula a little. You do not need to add zero months to the Month of the Current Date... you can simply put "Month(Current Date)". In the expression: "Date(Year(CurrentDate),Month(DateAdd('m',0,CurrentDate)),1)"; you can just have "Date(Year(CurrentDate),Month(CurrentDate),1)"

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[marckssg]

Thanks Mike.

Fair comment on the formula, it was in for good reason orginally, can't remember why though to be fair and lives on in that format thanks to lazy "text box" formula copying between reports!

Sadly Excels not an option, looking for something that can be scheduled so its automatic output every month, thanks for your reply, its still on my to do list, just not sure how!



Cheers

Marc

 

[marckssg]

Hi Mike,

Had a little time to sit down and look at this and gone with the now seemingly simple method of creating a command, with 6 unions, one for each month, put a field in each to identify the month then grouped/charted on this.

Works a treat.

Cheers

Marc