Hi all, I am trying to use the windowAvg() function but do it prior to the viz filters. For example, I have a table that I want all dates to get an even 30 days to calculate the 30-day trailing average. Currently the windowAvg() only has access to data “in” the viz. Meaning, the min edges of the table/time-series graph do not have access to the 30-days prior data because they are on the edge.
This question was asked in Feb 2024 but the community just suggested doing this calculation in the SQL prior to bringing in the dataset, with the solution author saying “I am unable to build a work-around with the calculated fields.”
I have seen others suggest that we can use a denserank() function and use that as a filter rather than a calendar_date field. This seems like another workaround. If it truly works, I would love some assistance from the experts here to show how that works. A requirement of mine is that we can have customer start_date and end_date values for the viz. So I am not sure how that would play into the denserank() function as well.
Otherwise, I would like to ask the Quicksight team if there is a possibility of getting a PRE_FILTER calculation level parameter added to the windowAvg() function.