Query regarding date filter

Q&A
, ,
1

Hi everyone,

I have a dataset that is queried from Redshift. The data is partitioned across two fields, year and month. As per a customer requirement, I have created a date field (calculated field) in QS dashboard that takes in year and month and constructs a date object. Now, when customers query using this field, the query time is super high because of the large data we have. When we use year and month to apply filters, the query times are considerably low as they are partition keys. Is there any way I could still use calculated field (date object) and still query using year and month to attain fast query times?

Thanks.

2

Hi @iamtarun,
Could you share the calculated field you’re currently using?
Additionally, have you tried setting up parameters based on your year and month fields which could then be used in your calculated field? That may reduce the timing.

1 Like
3

Hi @Brett, thanks for the response.

There’s a change in requirement, there is a field called accounting_date and I need to use that as the date filter. This field is separate one and we don’t construct it. It is present in the data. But if you take any row that belongs to X year and Y month, the accounting_date will always be in the Y month of X year. Is there any way to use accounting_date as filter in the QS dashboard and convert to year and month while querying Redshift.

Regarding the parameters, customers would want to use “date” filter as it would help them select relative filter like from 1st Sept, 2022 to 15th Jan, 2023. So I don’t think parameters for year and month would help unless I’m missing something.

Thanks.

4

Hi @iamtarun,
2 things to consider

  1. Is this running on Direct Query? If so, are you able to switch to SPICE instead and run incremental refreshes on an interval? The shortest time is 15 minutes, that could greatly improve issues with load times.
  2. Otherwise, you could write a custom SQL query in Quick Sight, build the field in the dataset or ingestion rather than in a calculated field for the date. Then utilize a parameter linked filter that would only query values based on the user selection on the dashboard.
1 Like
5

Hi @iamtarun,
Checking back in here to see if you had any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

6

Hi @iamtarun,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!