How to group by month in a Multilevel Pivot Table

[jesshal]

Hi all,

I have been asked to create a report displaying the number of Changes opened by each Assignment Group per month. They need to be divided up by Type of Change and month opened. It should ideally be something like this, except divided by month opened instead of Close Code.

The problem is that we do not have a Month Opened field, just an Opened field that holds a full date. This means when I use Opened instead of Close Code the report is too big to generate because it calculates the changes opened for every individual day.

Is there a way to organize by month rather than full date? I tried something like this with a Trend Report but could not find a way to group by Assignment Group and change Type at the same time with that kind of report.

Thanks in advance for an assistance.

[jesshal]

I also came to that same conclusion. I think it will be close enough at least. Thanks for the help!

Thanks,

Jesshal

[Jay Reddy]

Thanks! This is awesome and exactly what I needed!

[Terje Nilima Mo]

Hello.

There is an out of the box (at least in Utah) solution for this by creating a function field in the report.
In the configure-section of the pivot table report, click "Configure function field" and create a new function. I called mine "Created Month" with the definition
glidefunction:substring(sys_created_on,1,7)

This works if the date is in the format YYYY-MM-DD. What it does is return the first 7 characters of the date, which would be YYYY-MM or, for example 2023-06