QuickSight needs PRE_FILTER options for lag and period-over-period functions

Question & Answer
, ,
1

Our company uses QuickSight to perform analytics on sales data. One of the things that is common is comparing today or this month’s sales to last year’s sales.

Using the lag() function or something like periodOverPeriodDifference() would normally be very helpful in doing this, except one major problem. If you, for example, add the filter “Year = 2022” to only show the rows for this year’s data, then both of these functions stop working. This is because both of these functions are post-filter, and there is no calculation level option.

As such, both these functions are completely useless to us. Our work-around has been using a left join in our dataset query, using something like “ON sales.date = date_add(sales_last_year.date, INTERVAL 1 YEAR)” which ends up being computationally expensive and slow for particularly large datasets.

1 Like
2

@emilyzhu @Rajkumar_Haridoss @robdhondt FYI

3

Hello, I had the same problem, I handled it in the analysis instead of the dataset. I don’t know if it’s less expensive though

I’m using a “year” parameter and a calculated field which is a boolean for “is year or year-1”, and filter on this boolean.
This filter works for comparisons, I also use calculated fields for each measure if I only want the last year in the same visual (“sales year n” field), but it’s of course not optimal if you have a lot of measures.