how to retrieve data from two and three days ago?

[rjp]

Hi Gurus,

Is it possible to retrieve data that has an updated date equal to Yesterday AND two days ago AND three days ago.

I see there are built-in options for today, yesterday, last 7 days etc, but that goes back to far.  I just need for the past three days.  

Let me know if you think this is possible.

Thanks,

Rob

[rjp]

Thats what I thought also but this report brings back nothing....

....when clearly there are records with yesterdays date....this is trickier than i thought

thanks for your feedback

[Sravan21]

Hi rjp,

Your Requirement: The expected result is to show only records if they have ALL records for each of the past three days ONLY.  If missing 1 day, bring back nothing.

You cannot achieve this just by applying filters directly. You can run the below code in scripts - Background for each person and see whether the count is 4 or not (Including Today). 

If Count = 4, the person unanswered all the days within last 3 days including Today.

var ga = new GlideAggregate('UR_TABLE_NAME');
ga.addEncodedQuery('field_personLastName=Testo^field_unansweredDateRELATIVEGT@dayofweek@ago@3');
ga.addAggregate('COUNT');
ga.query();

if(ga.next()){
gs.print('count='+ ga.getAggregate('COUNT'));
} else {

gs.print('count=0');

}

Please Mark it helpful/Correct if it helps you.

Regards,
Sravan.

[rjp]

ok thank you I will look at this right now, but how would i get the results (records for people >4) onto a report that can be scheduled and sent out on a daily basis?

[Sravan21]

Hi rjp,

You can write a Client Callable script include and call the script in the report filter. Then Schedule that report and send it out on a Daily basis.

Step #1: Create the below Client Callable Script Include under System Definition> Script Includes.

Script Include: get3DaysAgoData
Client Callable: true
Description: Return Users unanswered all the days within last 3 days including Today.

function get3DaysAgoData() {

var ga = new GlideAggregate('<UR_TABLE_NAME>');
ga.addEncodedQuery('u_unanswered_DateRELATIVEGT@dayofweek@ago@3');// Replace 'u_unanswered_Date' with Unanswered Date field name as per your screenshot.
ga.groupBy('u_person'); //Replace 'u_person' with Person field name as per the your screenshot.
ga.addAggregate('COUNT');

ga.query();

var userArray = [];
while (ga.next()) {

        if (ga.getAggregate('COUNT') == 4) {//Today, Yesterday, 2 Days Ago and 3 Days Ago. Total count = 4 records.
                userArray.push(ga.u_person.name);
        }

}

return userArray;

}

Step #2: Use the script include javascript in List filter criteria.

Apply the below filter

Person.Name     is       javascript:get3DaysAgoData()

Unanswered Date       relative      after    3 Days Ago

If you see the results as expected, then export it to excel and share. Or Schedule that report and send it out on a Daily basis.

Please Mark it helpful & Correct if it helps you.

Regards,
Sravan.

[rjp]

Thank you Saravan for the thorough reply, I will follow the steps right now.