Time aggregate doesn't filter properly with large date span

Question & Answer
, , ,
1

I’m using a stacked area line chart where the X axis is a date + time that indicates when the measurement was taken. The script that populates the database runs every 15 minutes, so there’s not a crazy amount of data points here. The desired aggregate setting is “minute”, which is not ideal when selecting a large date rage.

This visual has a filter for start and end dates, which are controls that have a day granularity. If I select more than about a week for the date range, the graph doesn’t display all the days for some reason. The end date is always correct, but the start date is some arbitrary date AFTER the start date that I’ve selected. (In the visual settings, the number of data points for the x-axis is set to the maximum of 200.)

If I was actually coding the graph behind the scenes, I would instinctively add logic to control the granularity based on the number of days between the start and end dates, or based on the number of data points in the database. For instance, if more than a week, switch to hourly datapoints instead of minute.

I’m not quite sure how to fix this issue to prevent it from looking like there are missing datapoints. These are my ideas, but I don’t see any way to implement them:

  1. The aggregate setting should be dynamically changed based on the number of days between start and end dates. Even if I have “minute” selected, if there are too many data points, it should switch to hourly, then daily.
  2. Ensure that the end-user can only select a maximum of one week between start and end dates. This is not at all ideal, since the graph is used to track issues and trends.
  3. Add another x-axis (dimension) for hourly and daily aggregate drill-downs. QuickSight will not let me do this for the x-axis.

In these examples, the aggregate for the x-axis is HOURLY. It gets worse when set to MINUTE. Notice that there is indeed data from June 10th in the second graph, after I change the date range:

image
image1155×536 76.8 KB

image
image1151×534 52.6 KB

2

Hi @Anthony8888,
You could accomplish this with an ifelse statement that sets the parameter measure based on the number of days selected.

You’ll need to make a calculated field that defines how to measure the days within your date filter and what time measure is utilized. It would like something like this:

ifelse(
dateDiff(${StartDate}, ${EndDate}, 'DD') <= 2, truncDate('MI', {Order Date}),
dateDiff(${StartDate}, ${EndDate}, 'DD') > 2 AND dateDiff(${StartDate}, ${EndDate}, 'DD') <= 7, truncDate('HH', {Order Date}),
dateDiff(${StartDate}, ${EndDate}, 'DD') > 7 AND dateDiff(${StartDate}, ${EndDate}, 'DD') <= 30, truncDate('DD', {Order Date}),
dateDiff(${StartDate}, ${EndDate}, 'DD') > 30 AND dateDiff(${StartDate}, ${EndDate}, 'DD') <= 90, truncDate('WK', {Order Date}),
truncDate('MM', {Order Date})
)

To explain this a bit further…In the
First line: If your time length set by your start/end date is less than or equal to 2, measure by the minute.
Second line: if your time length set by your start/end date is greater than 2 and less than or equal to 7, measure by the hour. and so on.

When you’re creating for your own analysis, feel free to change those date measurements based on the range you would like.

Last and very important, when you’re applying this calculated field to the visual, make sure to change the granularity down to your lowest measurement (in this example it would be minute).

If you have any additional questions, feel free to let me know. If this solution works for your scenario, please feel free to mark it as the solution.

Thank you!

3

That worked exactly as I needed. THANK YOU!

For anyone else reading this, some clarity:

In the calculated field function, change {order_date} to the name of the parameter from your data, then replace the x-axis field with the calculated field you just created.

In my case the parameter name was {recorded_at}, which is the name of the underlying column in the MySQL database.

1 Like