Can I hide rows from my table without filtering them out? I have a table that has several dimensions and measures. One of the measures takes the average from the past 3 years. So if the date was April, 2022 my average would be (April, 2021 value + April, 2020 value + April, 2019 value )/3. I have parameters ${StartDate} & ${EndDate} that allow the user to select which dates to appear in the table. Currently they are connected to a filter on the table. The problem is that when I filter by date, it excludes dates I need to make my average calculation. Even though I don’t want to see them, I still want them available for calculations.
You should be able to use level aware aggregations in QuickSight to compute something before filters are applied. Level aware aggregations are usually functions with “Over” prefix, such as sumOver, minOver etc. The last parameter for the functions defined when the value should be computed, PRE_FILTER aggregations are computed prior to applying the filters.
I tried using this, but I seem to be getting an unknown error.
region:us-east-1
timestamp:1650402605011
requestId:9f913296-849f-4cc1-87f6-38487178c886
If it gets too complicated authoring level aware aggregations, another trick that I like to use is to define a table calculation (such as lag that you are already using) that returns a value for rows that should be displayed and null or 0 otherwise.
For example, this table shows year and number of flights.
Let’s say I want to show 3 year average but only for rows with year greater than “Start Date” (which is a parameter in my analysis).
I’ve defined following calculated fields:
- lag = lag(countIf(flights, ${StartDate}<=addDateTime(1, ‘YYYY’, {fl_date})), [{fl_date} ASC], 1)
- lag2 = lag(countIf(flights, ${StartDate}<=addDateTime(2, ‘YYYY’, {fl_date})), [{fl_date} ASC], 2)
- 3yearsAvg= (lag+lag2 + count(flights))/3
Here is how it starts to look
Now I can add filter on lag2 column to hide rows with 0 or null and I get
P.S. When using this trick I like to add one more filter to reduce amount of computations done by QuickSight. For example, when computing 3 years average and showing data for years 2014 and 2015, I know that I can exclude rows with fl_date prior to 2012.
1 Like