Forum Discussion

mirzay's avatar
mirzay
Cloud Apps
08-04-2022

Dashboard Filter Date in Last 180 days or NULL?

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
}
}
]
}

dashboards & reporting

5 Replies

Replies have been turned off for this discussion
  • Anonymous's avatar
    Anonymous
    08-08-2022

    Hi Mirzay,
    Try this instead:

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

    • mirzay's avatar
      mirzay
      Cloud Apps
      08-08-2022

      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's avatar
        Anonymous
        08-08-2022

        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's avatar
      mirzay
      Cloud Apps
      08-26-2022

      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's avatar
    harikm007
    Data Warehouse
    08-11-2022

    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