I have data at a TruckID granularity, and I am using a pivot table to group on certain columns. However, for these groups I would like to show a sample ID of a truck so that we can further deep dive into certain groups.
I have tried to use lastvalue function, but this requires me to have the truck id in the pivot which defeats the purpose of the pivot - i dont want truck id granularity, i just want one sample for each of the pivots.
example: i am pivoting on months - i want a sample id from that month
I tried to use the max function
Is there any way to do this?
I tried to do this with a calc field using toString(max(parseInt(vrid))) but this does not work, it works only for numeric strings. I also tried toString(max(vrid)) but this does not work as it is a string and max only takes numbers. I basically want to find a way to choose one fsid.
I have also tried to use:
firstValue(
vrid,
[ {QS - Ref Date} ASC],
[
{QS - Ref Date},
{QS - Ref Arc type}
]
but i get a VISUAL_CALC_REFERENCE_MISSING error. Both {QS - Ref Date},{QS - Ref Arc type} are used in the group by and VRID is used as Count Distinct in the values. I tried to also add vrid to group by and then hide it but it then destroys the purpose of the pivot because then each vrid gets a record.
Any suppport would be greatly appreciated. Thank you.
do you need to use a calculated field to show the sample ID? Maybe you can use a filter for your use-case, just use a filter on TruckID and only select one specific ID. If you want to use different IDs you can pin the filter on top or put on the sheet. Adding filters - Amazon QuickSight
Hopefully this can help you.
If I only select one fsid using a filter it will affect the pivot and only show the metrics relevant to that fsid.
The goal is to have a pivot with a overview, providing metrics ect, and to then have a sample FSID for the groups to allow for further deep dives into those groups.
Eg I will pivot on months, and for each month there will be metrics and a sample ID from each month to allow for deep dive. A filter will not achieve this, but correct me if I am wrong.
A workaround is to use denseRank paired with an ifelse to get a sample id & ‘Others’ in each partition and then use it (in hidden state) to drive first value.
See sample here - Q23692
Downsides are - 1) Expanding to bottom most level will still show two records and 2) will get messy if you are using both rows and columns in the pivot, and 3) can’t take advantage of conditional formatting as that is supported in detail records only.
It would have been much easier to solve this if we had string support for max/min/maxOver/minOver functions. I have added a Product Feature Request for same - Internal Link.
You can request your AWS contact to add a customer influence against this PFR.