I am currently using lastValue() function to pivot a text value. But this text value has to be added into the “ROWS” category otherwise it will error out.
Is there any way to avoid this restriction so the data can be grouped at a higher level?
1 Like
Hello @Yuchen
Can you share an example of the pivot table and the calculated field that you are using lastValue() in? Also, what kind of error is returning when you try to add that field to the rows field well?
1 Like
Hey duncan,
Thanks for the reply! Please see the 1st snapshot below. I need the red circled data to be aggregated on zip level, so that it only gives me 1 line per zip. But when I remove the station, it gives me the error in the 2nd snapshot. This is the formula I use in the VALUES field well: lastValue(Station, [Zip ASC], [{Plan_id}, Zip,Week])
Hey @Yuchen
I’m sorry about the late reply, I missed your response message. Were you able to find a solution to this?
That error means that the visual is missing a field that Quick Sight is expecting to be there. This is happening because you used station in your calculation.
Because you have multiple stations per Zip it looks like your lowest level of granularity would be at the station level. Do you want to exclude stations from each zip code?
Hey @Yuchen
Do you still need help with this issue or were you able to find a solution?
Hello @Yuchen
Since it’s been awhile since we’ve heard from you I am going to archive this topic. If you still need help with this question please post a new topic at the top of the community.
You can add the link to this topic in your new question.