Hello - I have a dataset which includes client names as well as the pages they have visited. I am attempting to create a pivot table that displays the most visited pages in order by each client (e.g. page rank as a row, client name as a column, and the page name as the value). I discovered the “lastValue” function to get text to display, but I run into trouble when QS requires the page name to be in a field well in order to display. Hoping someone can help me with the rank & page name calculated fields to get this to display as seen in the image below that I created in Excel. Sample dashboard attached with this dataset.
Hello @erweinstein, welcome to the QuickSight community!
Unfortunately, the exact format you are wanting from the Pivot Table is not possible because you are wanting to return a string value within the value field of the visual. This will only be able to return an aggregate field of some kind.
I was able to include 2 possible work-around solutions using the countOver function to determine the number of times a page was viewed, and the denseRank function to find the rank between clients.
The table on the left is going to be formatted like yours, but instead of showing page name in the values, it shows the number of times it was viewed. Then the pivot table on the right will show a different format (Page Name as Row, Client Name as Column, and Rank as value), but it contains all of the information that you were wanting to show.
