Filter a BloX widget to the same day of the week a year ago

Hi Silutions ,

You can experiment with the following approach:

• Include the same date filter as widget filter within the Blox where you want to implement the 'same day of the week a year ago' filter. Select any one random date in this widget filter (this step is important). We will replace the selected date with the calculated date using script.
• Apply the provided widget script to the Blox. Ensure to update the variables dashFilterName and widgetFilterName with titles of dashboard date filter and widget date filter.

widget.on('beforequery', function(sender, args){
	
	const dashFilterName = 'Date'
	const widgetFilterName = 'Date'
	
	const dashFilter = sender.dashboard.filters.$$items.find(el => el.jaql.title === dashFilterName);
	const widgetFilter = args.query.metadata.find(el => el.jaql.title === widgetFilterName);
	
	if (dashFilter && dashFilter.jaql.filter.members && dashFilter.jaql.filter.members.length > 0 &&
	   		widgetFilter && widgetFilter.jaql.filter) {
		
		let dashFilterDate = new Date(dashFilter.jaql.filter.members[0]);
		
		//Get Week number of selected date
		const dashFilterDateCopy = new Date(dashFilterDate);
		dashFilterDateCopy.setDate(dashFilterDateCopy.getDate() + 4 - (dashFilterDateCopy.getDay() || 7));
		const year = dashFilterDateCopy.getFullYear();
		const dayOfYear = Math.floor((dashFilterDateCopy - new Date(year, 0, 1)) / 86400000);
		const dashFilterWeekNumber = Math.ceil((dayOfYear + 1) / 7);
		
		const dayOfWeek = dashFilterDate.getDay();
		
		//get first week of last year
		lastYearDate = new Date(dashFilterDate.getFullYear() - 1, 0, 4);
		lastYearDayOfWeek = lastYearDate.getDay();
		
		//set first day of first week
		lastYearDate.setDate(lastYearDate.getDate() - lastYearDayOfWeek)
		
		//set same week a year ago
		lastYearDate.setDate((lastYearDate.getDate() + (dashFilterWeekNumber) * 7));
		
		//set same day of week a year ago
		lastYearDate.setDate(lastYearDate.getDate() - (dayOfWeek === 0 ? 0 : 7 - dayOfWeek));
		
		
		lastYearDateStr = `${lastYearDate.getFullYear()}-${('0' + (lastYearDate.getMonth() + 1)).slice(-2)}-${('0' + lastYearDate.getDate()).slice(-2)}T00:00:00`; 
		
		widgetFilter.jaql.filter = {
			
			explicit: true,
			multiSelection: true,
			members: [
				lastYearDateStr
			]
		}
	
	} 
})

 Result:

Let us know if this your expected result.

If you need to apply the filter only for a specific formula within Blox, you can update the script to replace date in formula instead of replacing widget filter.

Feel free to reach out if you have further questions, we're always happy to help 🙂
[email protected] 
Paldi Solutions, Number #1 Sisense Plugins Developer

Hello Silutions.

Can you update this and let us know if the solution provided by Benji_PaldiTeam worked for you?

Thank you.

Hi Jim Silutions ,

You can use the following functions to manipulate date filters on a widget or widget panel item calculation (filter in calculation) level in a persistent way on the dashboard level on 'filters changed' event using the following dashboard script:

const dashboardFilterDim = '[DimDate.Date (Calendar)]'
const targetWidgetOid = '65a0accf800ddf004150f2ae'
const widgetFilterDim = '[DimDate.Date (Calendar)]'
const widgetItemTitleName = '(sum([Order Revenue]), [Years in Date1])'
const widgetItemPanelName = 'value'
const widgetItemFilterDim = '[DimDate.Date (Calendar)]'

function getFilterMember(args, filterDim) {
    return args.items.find((i) => { return i.jaql.dim === filterDim }).jaql.filter.members
}

function getSameDayLastYear(date) {
    const dashboardFilterDate = new Date(date)
    dashboardFilterDate.setDate(dashboardFilterDate.getDate() - 365)
    const dashboardFilterDateCopy = new Date(date)
    while (dashboardFilterDateCopy.getDay() + 1 !== dashboardFilterDate.getDay()) {
        console.log(dashboardFilterDateCopy.getDay() , dashboardFilterDate.getDay())
        dashboardFilterDate.setDate(dashboardFilterDate.getDate() + 1)
     }
    return dashboardFilterDate.toISOString().slice(0, 19)
}

function updateWidgetFilter(widget, filterDim, members) {
    widget.metadata.panel('filters').items.find((i) => { return i.jaql.dim === filterDim }).jaql.filter.members = members
}

function updateWidgetPanelItemFilter(widget, panelName, itemName, filterDim, members) {
    const context = widget.metadata.panel(panelName || 'values').items.find((i) => { return i.jaql.title === itemName }).jaql.context
    for (item in context) {
        if (context[item].dim === filterDim) {
            context[item].filter.members = members
        }
    }
}

dashboard.on('filterschanged', (e, args) => {
    const targetWidget = dashboard.widgets.$$widgets.find((w) => { return w.oid === targetWidgetOid })

    if (args.items.some((i) => { return i.jaql.dim !== dashboardFilterDim })) { return }
    const dashboardFilterMembers = getFilterMember(args, dashboardFilterDim)
    const sameDayLastYear = getSameDayLastYear(dashboardFilterMembers[0])

    // Updating a Widget Filter
    updateWidgetFilter(targetWidget, widgetFilterDim, [sameDayLastYear])

    // Updating a Widget Panel Item Filter (calculated measure)
    updateWidgetPanelItemFilter(targetWidget, widgetItemPanelName, widgetItemTitleName, widgetFilterDim, [sameDayLastYear])

    // Persisting changes
    targetWidget.changesMade('metadata', 'filters')
    targetWidget.refresh()
})

The implementation here changes the 'filter' in the 'target widgets' filter panel to the same weekday of the previous year relative to the dashboard filter date, and then changes the calculated filter in the  

Benji & Daniel,

Thank you both.  This BloX effort has been put on hold for a bit.  I let you know how this comes out once it is restarted and I can test.

Regards, Jim

Hello Silutions ,

Did any of the solutions offered work for you? If so, please click the 'Accept as Solution' button so that other users with the same questions can find the answer faster. If not, please let us know so that we can continue to help.

Thank you.