Can we use lag function with pre-filter for aggregation.
Hello @Abhishek_24, unfortunately not. The lag function is considered a Level Aware Calculation - Aggregate function so it does not have access to the calculation level value.
Here is some documentation from AWS regarding the differences between LAC-A and LAC-W functions.
I will mark this as a solution for now, but if you need assistance altering your calculated field let me know!
Hi I have a lag function by year in a pivot table that works fine when a filter by year is not applied. When the filter is applied then the lag is not calculated, even when it is removed from the visual. Is there another way to use the lag so that it will be indifferent to the filter? Or another function that computes the lag of a measure that I can use for this purpose?
Thanks in advance
Before filter - Calculated results as expected:
After applying filter on year- fourth row is displayed- Lag not calculated
Does anyone have a workaround for this problem? Or can suggest a similar function that calculates the value of a measure of a previous year and is not influenced by filtering by year?
@Brett Any further thoughts here? ^
Hi @gnc,
As the lag function does not allow the use of ‘PRE_FILTER’, that function would not be able to avoid the rows that are being omitted by your date filtering, therefore there’s no data in the visual to run the lag calculation off of, leading to blank cells.
In regards to a work around, you could try running your lag calculation at the dataset layer, that way it’s creating the data for each row and may be able to still show the values since they’re applied to your rows that will not be filtered out.
Hi Brett
I decided to address the issue at the DB level, but I will also try the solution you suggested at the DS level. I hope there will be a solution for pre-filter in the future because this will make lag very useful.. Thank you very much for your reply.
1 Like