How to show 13 months of Incident counts?

[MW3]

I have a very specific requirement to show the past 13 months of Incident counts, ideally in a bar chart or trend line. Specifically:

* Omit current month

* Show full counts for prior 13 months

* Will not require manual updates of filters every month

* Report will be dynamic, that is, can be accessed at any time and will be accurate. (in other words, not run on a schedule)

I tried using the filter condition "relative after 13 months" but discovered that that only goes back 13 months from the current day. So the first month in the series will only be a full month count on the first day of the month.

I tried using the filter condition "between" with 2 dates, but then I would have to manually update the filters on each of these reports every month (not a good solution).

Any other ideas to try? Many thanks.

Margo

[Community Alums]

Hello,

This is feasible. Since opened on last 13 months condition is not available, we need to manually add parameter in the URL.

nav_to.do?uri=%2Fincident_list.do%3Fsysparm_query%3Dopened_atONLast 13 months@javascript:monthsAgoStart(13)@javascript:monthsAgoEnd(1)%26sysparm_first_row%3D1%26sysparm_view%3D

then you will get list of records (opened on last 13 months and not opened in current month).

Then click on the column heading context menu => Bar Chart

New Report will be opened then add you can add your filter condition, choose report type etc.

Feel free to mark correct, If I answered your query.

Will be helpful for future visitors looking for similar questions 🙂

[Community Alums · Accepted solution]

Hello,

Have you tried above solution?

I missed to mention creation of 2 script includes -


1. monthsAgoStart -

Script 

	
function monthsAgoStart(n){
var gdt = new GlideDateTime();

//SET THE DATE
gdt.addMonthsLocalTime(n * -1);
gdt.addDaysLocalTime( ( gdt.getDayOfMonthLocalTime() - 1 ) * -1 );

//CORRECT THE TIME
gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 00:00:00");

//RETURN THE DATE
return gdt;
}

2. monthsAgoEnd

script 

	
function monthsAgoEnd(n){
var gdt = new GlideDateTime();

//SET THE DATE
gdt.addMonthsLocalTime(n * -1);
gdt.addDaysLocalTime( 12 - gdt.getDayOfMonthLocalTime() );

//CORRECT THE TIME
gdt.setDisplayValueInternal(gdt.getDisplayValueInternal().split(' ')[0] + " 23:59:59");

//RETURN THE DATE
return gdt;
}

Kindly create above 2 script includes then check the result.

Thanks & Regards,

Akshay

[Community Alums]

Hello,

Have you tried above two script include and URL that I have shared?

Please let me know if you need any help.

Regards,

Akshay

[MW3]

Hi Akshay, You proposed a good and valid solution. Unfortunately, when I discussed it with our team, it turned out that we couldn't use it due to some other factors that were not in my original post. I have marked your response as the "Correct Answer" so that others can find it in the future. Thanks very much for your assistance.

Regards,

Margo