Navigation Action for Total/Subtotals

Question & Answer
, , ,
1

I have a pivot table with a status category as rows and a grouped date in the columns.

Screen Shot 2022-07-26 at 11.04.18 am
Screen Shot 2022-07-26 at 11.04.18 am466×553 14.7 KB

I have implemented a navigation action to another sheet with the raw data which is filtered using the parameters from the selected row/column of the cell. This action works as expected, except in the case of the total/subtotal column. Ideally this would navigate to the raw data sheet and show the data for the appropriate status and the date over the last quarter/year/all time. It appears that the navigation works correctly for the total row (at the bottom of the table) so I assume this issue relates to the subtotal/total column not registering the appropriate date range to which it is linked.

I have looked around on various forums using different descriptions and not come across anyone else asking about this particular issue. Has anyone else experienced/resolved this issue with their own analyses?

2

Okay update on this issue! It looks like entering a default value of NULL does the trick when the column header values are strings, as I guess the value then automatically removes itself from the filter condition? I was really excited when it worked on another dashboard of mine, only to find that Quicksight doesn’t allow a default date time parameter value to be NULL.

As such the way I found to implement this was to calculate months and quarters from the date value of interest, then convert all of those into strings. I also had to create a CSV file to correctly order the months and quarters when you add them to a pivot, as the default ordering for strings are alphabetical or reverse alphabetical. Once you create parameters for the quarter and month values you insert a default value of NULL, and then link them to your navigation action as normal.