Set Search Date Criteria Begin and End dates as weekly blocks (current week), by default

[DesiLogi]

I don't know if this is possible but I need to set Search date criteria Begin and End dates as weekly blocks (current week), by default. Meaning, the 'Begin Date' of the criteria has a default of the Sunday before (or on) the current date and the 'End Date' has a default of the Saturday after (or on) the current date. 

I'm using a Pivot Table to show totals over a workweek period, using a 'ServiceDate' date field for the columns. The user needs to see just the current week (7 days) so I have 'ServiceDate' in the Search or Filter section, with criteria for 'Begin Date' and 'End Date.' The user could type in the previous Sunday's date (the Begin Date) and the following Saturday's date (the End Date) to filter the results by that week. However, I need this to happen automatically when the datapage is loaded. 

I imagine you could populate the 'Begin Date' and 'End Date' criteria by either External Parameters or Default Values but I can't figure out how to calculate the Sunday-through-Saturday dates based on the current date. Any help would be really appreciated-- 

[SinJunYoung]

23 hours ago, DesiLogi said:

I don't know if this is possible but I need to set Search date criteria Begin and End dates as weekly blocks (current week), by default. Meaning, the 'Begin Date' of the criteria has a default of the Sunday before (or on) the current date and the 'End Date' has a default of the Saturday after (or on) the current date. 

I'm using a Pivot Table to show totals over a workweek period, using a 'ServiceDate' date field for the columns. The user needs to see just the current week (7 days) so I have 'ServiceDate' in the Search or Filter section, with criteria for 'Begin Date' and 'End Date.' The user could type in the previous Sunday's date (the Begin Date) and the following Saturday's date (the End Date) to filter the results by that week. However, I need this to happen automatically when the datapage is loaded. 

I imagine you could populate the 'Begin Date' and 'End Date' criteria by either External Parameters or Default Values but I can't figure out how to calculate the Sunday-through-Saturday dates based on the current date. Any help would be really appreciated-- 

Hi, have you tried using the current week as the comparison type? 

[DesiLogi]

Hi SinJunYoung, 

Many thanks for that tip- I did not catch that as an option for the Search/Filter date.  It works really nicely except for one thing-I need the user to be able to change the week they want to Search/Filter by so the date needs a Begin/End criteria that's visible. I just need the current date to open by default so they don't have to enter it in themselves (bad design if they did). 

Here's some js I put together to get the Sunday before the current date and then add 6 days to it for the following Saturday, and then to put that into the Date Criteria for Begin and End dates. This allows the user to change the Search/Filter to different dates.  

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var curr = new Date();
day = curr.getDay();
firstday = new Date(curr.getTime() - 60*60*24* day*1000); 
//will return firstday (ie sunday) of the week
lastday = new Date(curr.getTime() + 60 * 60 *6 * 24 * 1000); 
//adding (60*60*6*24*1000) means adding six days to the firstday which results in lastday (saturday) of the week

var v_weekstart = firstday.toLocaleDateString('en-US'); 
var v_weekend = lastday.toLocaleDateString('en-US'); 
//removes the time from the day/time value so it can be used in the date criteria search

var v_weekend2 = (firstday.getMonth()+1) +"/"+ (firstday.getDate()+6) +"/"+ (firstday.getFullYear())
//this was added because in testing the variable 'lastday' wasn't always correct

document.forms[0].Value2_1.value = v_weekstart;
document.forms[0].Value2_2.value = v_weekend2;
//this puts the two date values in the two criteria so they can be used to search/filter

});
</script>

***I haven't thoroughly tested this yet so if anyone needs this check it out in detail before deploying. The code for the variable 'lastday' did not give me an accurate date last week, but this week it does, so it seems unreliable.  Therefore I used another method to add 6 days to come up with the end date of the current week  (variable v_weekend2). I left the original code for lastday in there in case someone needs it. 

[kpcollier]

Just an update on this. There was a recent article showing how to do this, that is working well for me.

const today = new Date();
const first = today.getDate() - today.getDay() + 1;
const last = first + 6;

const firstDay = new Date(today.setDate(first));
console.log(firstDay); // Mon Jan 17 2022 18:03:00

const lastDay = new Date(today.setDate(last));
console.log(lastDay); //Sun Jan 23 2022 18:03:00

var dayF = document.getElementById('cbParamVirtual1');
var dayT = document.getElementById('cbParamVirtual2');

dayF.value = firstDay;

Source: Get First and Last Day of current week with Javascript

[DesiLogi]

@kpcollier,

This is a great solution, as mine above wasn't always reliable. I just tweaked the date format at the end so the value is mm/dd/yyyy and can be used in a Search field (in this case starting Sunday and ending on Saturday): 

const today = new Date();
const first = today.getDate() - today.getDay();
const last = first + 6;

const firstDay = new Date(today.setDate(first));
console.log(firstDay); 

const lastDay = new Date(today.setDate(last));
console.log(lastDay); 

var dayF = document.querySelector("[id*='Value3_2']");
var dayL = document.querySelector("[id*='Value3_3']");

dayF.value = firstDay.toLocaleDateString('en-US'); 
dayL.value = lastDay.toLocaleDateString('en-US'); 

 

[DesiLogi]

Hi @kpcollier,

One odd thing I can't figure out, though, is today (9/1/2023) when the Search form is opened I get the correct first date for the week of 8/27/2023 but the "last" date fills as 8/2/2023 instead of 9/2/2023. Yesterday, on 8/31 it did fill the last date as 9/2/2023. So it seems that the new month hangs up the "last" date somehow. Any idea how to fix it? 

[kpcollier]

@DesiLogi To be completely honest, it has been a while since I used this. I don't remember exactly which app I used it on. Give me a bit and I'll track it down and see what I've done!