Query Sent Not Containing SQL

Question & Answer
,
1

I am attempting to trend an important metric over time using a Line Chart, and in order to calculate it I am needing to use a calculated field to sum all charge amounts in the previous X number of months. X can be defined by a user on the dashboard with a parameter + control. The control is a slider, with options of 1-12.

For example: when the slider is set to 3, and looking at 10/2022 on the line chart, the field would sum for 09/2022, 08/2022, & 07/2022

I can’t use a window function, because I need the value to remain consistent upon drilling up/down by date. I need to let users utilize the native drill down function on the visual, so I can’t use parameters for drilling up/down.

My formula looks like this, which returns what I would expect for 1 month look back:

{Daily Charges 1 Month Lookback}:

periodOverPeriodLastValue(
max(
sumOver(
{Charges},
[{Group-by},
truncDate(“MM”, {Date})],
PRE_AGG
)
),
{Date},
MONTH,
1
)

{Daily Charges 2 Month Lookback}:

periodOverPeriodLastValue(
max(
sumOver(
{Charges},
[{Group-by},
truncDate(“MM”, {Date})],
PRE_AGG
)
),
{Date},
MONTH,
2
)

I do the same method for months 3-12, and then have a series of calculated fields adding these fields together. So for a 2 month look back, I would have a corresponding field which works as expected:

{Daily Charges M1 + M2}:

ifelse(isNull({Daily Charges 1 Month Lookback}),0,{Daily Charges 1 Month Lookback})
+
ifelse(isNull({Daily Charges 2 Month Lookback},0,{Daily Charges 2 Month Lookback})

The issue is, I get an SQL Exception Error when I go beyond Month 5.

I checked our data source (Redshift), and the query is received and runs for roughly ~10 seconds, and then disappears completely from the UI. It doesn’t abort or successfully run. I can’t see any SQL at all in the query, its just one line with a /* comment.

When reviewing the queries for Months 1 - 4, I do see SQL syntax.

Has anyone experienced this issue with Quicksight? Is there a hard limit to the amount of time Quicksight takes to generate a query? I receive the error way before the 2 minute query timeout limit, and like I had mentioned, the query only runs on the backend for 10 seconds before disappearing.

Thank you!

2

@hafeng Please work with this user to figure out the issue. Thanks.

3

There is no limit to the amount of time.

query is received and runs for roughly ~10 seconds, and then disappears completely from the UI

is this the behavior for month > 5? I assume it works fine when month < 5.

I would suspect the killer is the total calculations if your data is returned then disappear itself.
QuickSight send a separate query to compute PoP totals. When the second query fails, it could affect the individual cell results.

It looks like an issue in the QuickSight backend. Could you describe your accountId, region, timestamp, URL and screenshot if available to your AWS contact related to the QuickSight “query availability”? We can help you further by looking at the logs.

1 Like
4

Yes that’s correct. 4 months or less and it loads on a table, as well as a line chart. At 5 months, it appears to function intermittently, but receives an error more often than not. >= 6 months has not successfully ran once.

I’ll reach out to my company’s AWS contact and provide them with the information so they can include it in the case.

Very glad it sounds like a fixable issue, as opposed to an unsolvable problem. I appreciate your advice, thank you.