Dashboard Filter Date in Last 180 days or NULL?

[mirzay]

I need to create a dashboard filter that will filter the Closed Date In Last 180 days OR if Closed Date is NULL

Would you know how to include the OR Condition in a Dashboard Filter?

I tried using the Advanced formula below and some variations of this but getting the Invalid Syntax or Invalid Query messages:

{
"explicit": true,
"multiSelection": true,
"members": [
"N\\A",
{
"last": {
"count": 180,
"offset": 0
}
}
]
}

[Anonymous]

Hi Mirzay,
Try this instead:

{
"or": [
{
"last": {
"count": 180,
"offset": 0
},
"custom": true
},
{
"include": {
"members": [
null
]
}
}
]
}

[mirzay]

Thank you for the response Priyanka. I am not getting the expected result.
(Converted to Months instead of days but tried both ways)
I am expecting the range to be last 6 months to end of next month.  What I get are these dates.

[Anonymous]

You can use this script to get the range to be last 6 months to the end of next month:

{
"or": [
{
"last": {
"count": 6,
"offset": 0
}
},
{
"next": {
"count": 1,
"offset": 1
}
}
]
}

[mirzay]

I am using your filter formula to filter for a date that is Null or the date occurred in the last year. The result is picking up all years and also the null value. How can I restrict to filter to last year and null values?

{
"or": [
{
"last": {
"count": 1,
"offset": 1
},
"custom": true
},
{
"include": {
"members": [
null
]
}
}
]
}

[harikm007]

@mirzay  Another approach is to write a dashboard script to select last 180 days and N\A in filter.

dashboard.on('initialized', function (se, ev) {
	
	filter = se.filters.$$items.find(el=>el.jaql.title == 'Date')

	day_count = 180
	
	max_date = new Date(new Date().toDateString())
	min_date = new Date()
	min_date.setDate(max_date.getDate()-day_count);
	
	var new_date_list = [];
	var new_date_list_string = []
	
	
	temp_date = min_date
	while (temp_date <= max_date) {
        new_date_list.push(new Date(temp_date));
		new_date_list_string.push(moment(temp_date).format('YYYY-MM-DDT00:00:00'));
        temp_date.setDate(temp_date.getDate() + 1);
    }
	new_date_list_string.push('N\\A')
	
	breakbyFilter = {
		'jaql': filter.jaql
	
	}
	
	filter.jaql.filter = {
							"explicit": true,
							"multiSelection": true,
							"members": new_date_list_string
						}
	filter.jaql.level = 'days'
	
	var filterOptions = {
							save: true,
							refresh: true,
						}
	
	se.filters.update(filter, filterOptions)
})

-Hari