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

[Silutions]

We have a requirement to filter a BloX widget to the same day of the week a year ago.  Example:

• The dashboard filter is selected to Friday 11/24/2023
• We want a particular widget to be filtered to the same day of the week last year:  Friday 11/25/2022
• Note:  we need to take leap years into account

See the screen shot below for further clarification

I assume a widget script is required.  Can anyone help me with this?

Regards, Jim

[Benji_PaldiTeam]

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

[Silutions]

Benji,

Thank you for this.  I have tried on BloX and with Pivots.  I followed your instructions.  No joy.  Can  you send me your BloX templates from your example?  With those I should be able to figure it out.

Regards, Jim

[Silutions]

Benji,

Also, we do have some filters that are in calculations.  It's not clear to me how to change the code to point to the calculation filter instead of the widget filter.  Can you explain?

Thanks again, Jim

[Benji_PaldiTeam]

Attaching the Blox template for your reference. 

Also here is the summary of steps:

• Add Date dashboard filter (level = Days). Make sure to select only one day at a time.
  
•  Create Blox with attached template.  
• Add Date widget filter to Blox (level = Days). and select any random date
• For the attached Blox template, add Date dimension under 'Items' panel. One with level Days and other with level Weeks

• Add widget script (from my previous reply) to Blox widget.

Please let me know if the solution works.

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

[DRay]

Hello @Silutions.

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

Thank you.

[rapidbisupport]

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  

'(sum([Order Revenue]), [Years in Date1])' item on the 'value' panel on the targetWidget.

You should be able to use these functions to:

- get the filter member from the dashboard,

- get the same day last year,

- update a widget filter given a widget, filterDim and members you want to update and

- update a widget panel item filter, given a widget, panel name, item name, filter dim and the members you want to update.

You'll see the implementation in the body of 'dashboard.on('filterschanged', (e, args) => {'

This implementation should work on any widget, not just BloX controls.

I'm also happy to walk through over a quick call if you'd like - I would genuinely be interested in hearing more about your use case, how you and your customers leverage Sisense more generally and potentially trade/share some notes.

Let me know how you go?

Thanks,

Daniel

RAPID BI

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

[Silutions]

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

[DRay]

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.