You can use date attributes to limit your report results to a selected date range or rolling periods. If you are creating calculated elements, you can use dates in formulas and functions (see Explore functions reference).
In Explore, you can view dates broken down by years, half-years, quarters, months, weeks, weekdays, and days. You can also view a complete date in yyyy-mm-dd format with the Date attribute. In addition to dates you can view timestamps broken down by hour, minutes, and seconds or view date and time together with the Date_Time attribute.
You can add dates just like any other attribute (see Adding attributes). If you do not want to view specific dates, you can exclude them from your results.
To select or exclude dates from your results
- In Report Builder, add your date attribute to any attribute location, for example, the Columns, or Rows panel.
- Click the attribute you added to open the attribute properties panel.
- Click the Selected panel to select specific dates to include.
- Click the Exclude panel to select specific dates to exclude.
Editing date ranges
Once you add a date attribute to your report, you can specify the period of time by editing the date range options. You can edit your date ranges by clicking on a date attribute, then selecting Edit date ranges.
There are two types of date range options, Simple and Advanced. In the simple panel, you can choose from a list of named periods or use the custom option to select a unique time period. You can include these date ranges in your results, but not exclude them.
In the advanced panel, you can choose separate beginning and end dates. For each date you can select from a list of named periods, calendar dates, a rolling time period, or all available history. In a rolling period, you can select options, such as seven days in the past or a year in the future.
You can use date ranges to measure period-on-period results by adding a repeat pattern. With a repeat pattern, you can compare a data point this year to the exact same point last year. For example if your date range is This year to Today, and today is September 16th, a repeat pattern of one year in the past will compare figures for this year with January 1st to September 16th of last year. Without a repeat pattern, your report would compare the figures for this year and the overall total of last year.
- Click the Advanced panel in Edit date ranges.
- Select your date range settings.
- Click the Add repeat pattern button.
- Select your repeat pattern options.
- Click Apply
You can click the Add repeat pattern button again to add an additional repeat pattern.
If you do not have a seven day working pattern, you can use a repeat pattern to look at a single day's results.
- Yesterday will show results for the previous day.
- Yesterday (working day) will show either the previous day's results if the current day is between Tuesday through Saturday or the previous Friday if the current day is a Sunday or Monday.
- Yesterday (workable day) will show the previous day's results if the current day is between Tuesday and Sunday or the previous Saturday if the current day is Monday.
Hi @... @...
It seems this article sent provides instructions on how to do adjust time for a query.
However, I'd like to adjust the "LAST WEEK" date range in the time filter for the entire DASHBOARD, not the query. My preference is start date: MONDAY, and end date: SUNDAY.
For reference, please see the screenshot below. When I click "Last Week," the result is 1/24 - 1/30 which is a start date of Sunday, and an end date of Saturday -- instead, this should display 1/25 - 1/31. Is this possible?
Is there a way to create a dashboard that can have its dates set for on-demand reporting but can also generate scheduled email reports for preset ranges like "last 7 days" or "last month?" When I've done this before, I had to create duplicates of all my queries and a duplicate dashboard - one set of queries with no date restrictions so the Dashboard can set the dates, and another with the specific date restrictions for the scheduled email reports. I'd much rather do it with a single set of queries if there's a way to do it.
Thanks for reaching out! Selecting Last Week within the time filter should default to Monday - Sunday but as you can see the dates shown reflect Sunday - Saturday. We've raised this issue with our Product team and they will be working on a fix. I'd be happy to create a ticket on your behalf so you can stay updated on the progress.
In the meantime, we recommend manually selecting the dates within the time filter to reflect Monday - Sunday.
We're very sorry for any inconvenience.
I recently created a series of queries that have a pre-set date range of the last 7 days, and wanted to know if there's any way to include the date range that's covered somewhere on a dashboard that goes out via email each week. I know I could include the covered dates in table format, but these particular queries are either KPI (average for the week) or sparklines, so they don't display the date range.
Could you use a time filter at the dashboard level to filter to the last 7 days instead of filtering at the query level? When adding a time filter to a dashboard you can choose to show the selected date range on the dashboard. Here are some articles that might help for setting up time filters:
I'm having the same issue as Ervin with the "Last week" time filter, but I want to add some more info on what I have observed.
When I select "Last week", the dates shown as currently viewing are Sunday to Saturday (2/28 to 3/6), and these are the results my queries show:
Then, when I select "Custom", starting on Monday 3/1 and ending on Sunday 3/7, my queries show the same results:
And finally, if I select "Custom", starting on Sunday 2/28 and ending on Saturday 3/6, I get different results:
So, from this, what I understand is that the "Last week" filter is correctly showing results for Monday to Sunday, even though the dates shown as currently viewing are Sunday to Saturday. To everyone confused here, if your work week is Monday to Sunday, it looks like you can continue to use "Last week" confidently.
@... please make sure your Product team is aware of this, and I hope the display issue can be fixed soon.
Thanks for adding these here. Like what Taylor mentioned, this has been raised to our product and they are working to address this as soon as possible. Sorry again for any trouble that this may have caused. We'll also create a ticket on your behalf and link it up to the problem ticket that the product team is working on, so that you'll be notified as soon as a fix is ready for roll out.
Currently, my organization pulls the data individually for every day of the month. We need each separate day due to the way we currently calculate our metrics, but I'm having trouble finding a way to edit the date options in order to do that in one single report (choose a month and have that break out each individual day in one report), so that I can do a single export with all the individual data per day for each of our analysts, rather than having to adjust the date options for a single day, export, then rinse and repeat for the entire month.
If you add a - Date or - Day of month attribute (example: Ticket solved - Date, Update - Day of month) under rows/columns, then your metrics should be sliced per date. When you visit your report to export the data daily, the query should show how the numbers are for each day/date. If your report is also sliced by agent or assignee, then your report will display the stats of each agent for each day of the month.
When will the dates shown when selecting Last week vs. the dates reported on be corrected?
Can you further explain what exactly is the issue you are experiencing?
Dane, Fixing: "Selecting Last Week within the time filter should default to Monday - Sunday but as you can see the dates shown reflect Sunday - Saturday. We've raised this issue with our Product team and they will be working on a fix. "
Hi, I would also like to know the above. The Last Week date range selects Sunday-Saturday instead of Monday-Sunday. In previous comments this was stated to be an error. When will this be corrected? Or is there the ability to choose?
A fix has already been rolled out. Please refer to Announcing custom start of the week in Explore for more information.
Is there any further documentation on how Advanced date range selection is supposed to behave?
I have noticed that when selecting "From the beginning of 1 years in the past" to "To the end of Today" that get unexpected results. For example, I would expect to see a list of tickets generated in the past year, say from 'today' in 2021 to 'today' 2022. What I am seeing instead is a list of tickets From Jan 2021 to 'today' in 2022.
I've built a lot of reports using the example above and if the behavior that I've explained above is the intended behavior then I will have to work around this by using from "365 days in the past".
Is this intentional behavior or is this a bug?
I'm trying to filter with the dates to get the number of open tickets within a time range (without filtering with created date or solved date), is this available ?
The closest that you can build in Explore is through the Backlog dataset. You can report on the Backlog numbers to see the number of unsolved tickets within a certain period. If you need to see the count of tickets that were in Open status, then you can also slice the backlog data by Ticket status. But note that the Backlog is recorded each day – if a ticket remains unsolved for several days, that ticket will be counted under each Backlog date. You can read more about this here: Analyzing your ticket backlog history with Explore.
Please sign in to leave a comment.