I have a pivot table in Quicksight that tracks PoP performance of metrics upon choosing two periods. I have the period among the dimensions of the table which is also part of the window function. I want to now show the latest of the two periods chosen in my visual without it affecting the calculations in any way. I only want to apply this filter to clean out any values I might have in the lesser of the two period.
Hello @Tanisha_Shetty , is this functionality that you want to always exist on this table, is it just for testing, or is it a functionality you want the Dashboard user to be able to control? I have some ideas but want to make sure I fully understand the use case for the solution.
There is an option to right click the period you do not want in the visual and click âExcludeâ to just show the period you want, but that is not a dynamic solution so that may not account for your use case.
I want this functionality to always exist on this viz and show me greater of the two periods chosen by the user. Since Iâm calculating using lag function and using Period ID as a part of the window partition clause, excluding a certain period disturbs the calculations.
Have 2 parameters for Start Month and End Month that the user will set via controls on the dashboard
Those parameters will be used to handle the calculated fields you built out to handle the PoP functions
Then create a filter for the visual you are working on and set your date period to the end date parameter that the user controls
That should ensure that your calculations are working according to the date periods you set but that only the date period you want displayed should show as expected.
Hi @Tanisha_Shetty - I think @DylanM 's solution will work but you need to write the calculated fields differently (you are using Lag which is a Table Calculation and requires the prior periodâs data to be in the query, so a normal date filter will not work in this case).
Another option would be to implement a âdate hiderâ, which looks just like a normal filter, but hides the data from the visual layer rather than removing it from the query. Create a calc that looks like this and then add a filter on top of this field:
maxOver({date field}, [{date field}], PRE_AGG)
That is a fancy way of returning the exact same date value, but it forces it to happen later in the pipeline so effectively it just hides the data from the visual rather than filtering in the query to the DB.
Another way would be to write a Rank calculation that is sorted by date value DESC, then filter it to 1 which will only show the first date value. Similar concept that Rank is a table calculation and will happen later in the pipeline.
@Tanisha_Shetty would you send the calculations that you attempted on this visual? You could also try to use PRE_FILTER instead of PRE_AGG and add a filter to just that visual for N Month (Jun). That could continue to be controlled by the Start and End months you are setting for the calculated field functionality.
Hi @Tanisha_Shetty , I am going to archive this question but if you need help with this again create a new question topic and we will help you find an answer!