Custom Charts - how to query only last 6 months of data?

[kani1]

Hi,

I have a custom report created   which shows the report sine the beginning. I want to modify this to show only last 6 months of data. Is there a way to do this? Please help me.

[User179407]

Hi,

If you want to filter the data based on Create date, you can add a Filter condition on your report.

Let me know if it helped.

Suraj Chauhan

[kani1]

Thank you Suraj for your reply. I am creating custom charts where we need to write queries. Here is the sample code which I use to query the data. I need help to filter only last 6 months data. I see that there is a gs function monthsAgo. Could anyone show me some insights on how to use this function?

var open = new GlideAggregate('change_request');

  open.addQuery('u_incident_mim', '!=','');

  open.addQuery('state', '!=','5');

  open.addAggregate('COUNT');

  open.addTrend('opened_at','Month');

  open.query();

[User179407]

Hi,

You can add

open.addQuery('sys_created_on','>=', gs.monthsAgo(6));

Refer section 3.34.3 as an example.

GlideSystem Date and Time Functions - ServiceNow Wiki

Let me know if this helped.

Suraj Chauhan

[rcm]

Hi - I am testing this out using the standard (oob) custom chart - Incident Backlog.

I am able to change the values to show by date i.e. by day rather than by month and I would also like to show this only for the last 30 days but no matter where I put the query

opened.addQuery('sys_created_on','>=', gs.monthsAgo(1));

it still hows the data from the start of our servicenow use.

Did anyone get this working ?