How to fetch 1st occurence of "Opened By" user for every month applied on "Interaction" table

[Pranav28]

I want to create a report (For the Walk Up Records) in which I have to display how many users created walk up records for the 1st time per month.
Eg. In November month User A and User B created walk up records for the first time. So,in report, November month should show total count of 2. In December month, User A, B,C created walk up records. In this case, this will be 2nd occurrence of A and B but 1st occurrence of user C. So, in the report for the month of December, total count should be 1. Because only user C is new. A and B are repetitive.

I am trying to write a script include and calling it from the report.
Following is the script I have written;
The script is giving me the unique user records But it's not working in "Per Month" scenario.

(I need the  script to compare the user in previous months.If we get the user, check if there are any records created by the same user in all previous months. If there are previous records, do not display, if this is 1st record created by user, then display it.)

Script:

function getUniqueValuesWalkUp() {
        var uniqueValues = [];


        var ga = new GlideAggregate('interaction');
        ga.addAggregate('COUNT','opened_for');
        ga.groupBy('opened_for');
        ga.addHaving('COUNT', '>', '0');

        ga.query();

        while (ga.next()) {
            // check if the row is for a unique value and not for an overall count
            if (ga.getDisplayValue('opened_for')) {
                uniqueValues.push(ga.getDisplayValue('opened_for')); // add the value to the array
            }
        }


        return uniqueValues;
    }

(Note - In the above script, I have used users of "Opened For" column, but I want it for "Opened By" column users.


   Thanks!

[Brad Bowman]

Here's an example that should get you closer to what you're looking for.  This runs as a Fix Script with some temporary logs to show the progression.  I'm hard-coding one month date range, but using this as a report script include, you could pass in a start date and end date from the report parameters.

var usrArr = [];
var prevUsrArr = [];
var usr = '';
var intGR = new GlideRecord('interaction');
intGR.addEncodedQuery("opened_at>=javascript:gs.dateGenerate('2022-01-01','00:00:00')^opened_at<=javascript:gs.dateGenerate('2022-01-31','23:59:59')");
intGR.orderBy('opened_by');
intGR.query();
while (intGR.next()) {
	if (intGR.opened_by == usr){
		gs.print('same user found ' + intGR.opened_by.name)
		break; //this record was opened by the same user as the previous record evaluated, so skip to the next record
	} else {
		usr = intGR.opened_by.toString();
		usrArr.push(intGR.opened_by.toString());
		gs.print('new user added to array ' + intGR.opened_by.name)
	}
}

var previntGR = new GlideRecord('interaction');
previntGR.addQuery('opened_by', 'IN', usrArr.join(','));
previntGR.addEncodedQuery("opened_at<=javascript:gs.dateGenerate('2022-01-01','00:00:00')");
previntGR.query();
while (previntGR.next()) {
	prevUsrArr.push(previntGR.opened_by.toString());
	gs.print('previous user added to array ' + previntGR.opened_by.name)
}

var arrayUtil = new ArrayUtil();
var uniqueValues = arrayUtil.diff(usrArr, prevUsrArr);
gs.print('uniqueValues = ' + uniqueValues.length + ' ' + uniqueValues.join(','));

[Pranav28]

Hi Brad, This is working exactly as I wanted. But I just wanted to know how to customise the date range and use it in the script? Without using hard coded values, how to make this code run from 1st January 2021 to 31st December 2021? And the result must be month wise. Let's say, code is running from 1st january to 31st January, whatever result is getting, it should be stored in Array, then, again it should run from 1st Feb to 28th Feb, capture data and store it in array and so on till 31st December. 

And at last, it should be displayed collectively per month when  I am calling that single script in the report.

Right now, I am getting results by only doing hard coded values for every month. And I have to create separate script include for every month.