Hide one of the two chosen fields in Pivot table

Question & Answer
1

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.

image
image1534×239 16 KB

Here I want the visual to show values only for Jun 30,2023 and hide May 31, 2023.

2

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.

3

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.

4

Hi Dylan,

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.

Appreciate your help!

Thanks,
Tanisha

5

Hi @Tanisha_Shetty , will you try this for me:

  • 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.

6

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.

1 Like
7

Thanks @DylanM and @Jesse,
I tried but neither of them work without affecting the calculations

image
image965×543 88.7 KB

image
image804×465 84.3 KB

Appreciate your input!

Thanks,
Tanisha

8

@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.

9

Hi @Tanisha_Shetty , were you able to resolve the problem you were experiencing in QuickSight?

10

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!