I’m having some issues with a pivot table vision. I have a month-year field in columns, sales in values, and a parameter controlled selectable dimension field in group by. We bill invoices for the following business day and with it being 11-30, we have several sales records for 12-1. They are clearly visible in the dataset; I can see them when creating a regular table visual. The records show in the pivot for all of my other dimensions but when i select customer, the december column gets dropped. I tried to troubleshoot with ai but it’s indicating that it’s related to no records associated with the dimension compared to other months but that is the case for every other dimension as well. Does anyone know what could cause this?
An important note, if i throw a date filter on the table and select December, I can see the records but when no filter is applied, the column/data disappears.
To answer your question, I think it may depend on a few components. One component could be due to how the visual types are interacting with your data. As you stated your table visual works and that could be due to how table visuals show all rows of data exactly as they exist. For pivot tables, they do include some visual logic in the sense that they do apply both aggregation and grouping to the data. Another component could be how the parameter interacts with the pivot table logic that results in hiding the December data.
There can be a couple of reasonings to why you are getting this output. Since you know the visual works in a table visual type, then you can go that route and not do it as a pivot table. However, if you really want to do this visual as a pivot table then you can either upload your dashboard to the Amazon Arena Sandbox and I can take a further look into it (refer to this link for more information on the arena: Amazon Arena Sandbox: An embedded instance of the Quick Sight capability, within the Amazon Quick Suite Community - What’s New - Amazon Quick Suite Community). Or if you do not feel comfortable with that, then can you provide more details into the fields and parameter you are trying to use.
So the next day, 12-1, with a few additional customers reporting sales the pivot is functioning normally. Does this give you any more insight? I can’t utilize sandbox for this.
Thanks for the update. If the pivot started working normally the next day once a few more customer records came in, I interpret that as it is something tied to the customer-level data rather than the pivot setup. When a pivot behaves like that it usually means the original set of customer rows didn’t have something the other dimensions had (like how the month-year value was coming through or how those records were grouped).
It would still help to see what the pivot looks like with Customer selected versus one of the other parameter options. And if you’re using a calculated field to switch dimensions based on the parameter, sharing that logic would help rule out anything going on there. If you can provide screenshots of both, that would be really helpful.
Based on your update, I think it points toward the issue being in the underlying customer data, not the visual. With screenshots into the pivot views or the calc field on my end, I think I should be able to get a better sense of what caused the December column to drop originally.
Apologies for the delay getting back to you. For pivot tables, their behavior revolves heavily around grouping and aggregation. If any of the dimensions being used contain empty values, the pivot cannot form a complete grouping structure. This often causes entire rows or columns to disappear, which aligns with what you described earlier about certain dimensions not having records associated with them.
A potential solution is to make a small adjustment to your dimension-toggle calculated field. The calculation works well when each dimension has valid data, but since the final argument returns NULL, that becomes a problem for pivot tables. Even if you select a dimension like Customer, if some of the underlying records for that period do not contain a value or have not been populated yet, the IFELSE returns NULL. Pivot tables cannot group or aggregate on NULL, which is likely why December did not appear until additional records arrived the next day.
Try replacing the final NULL in your calculation with a placeholder string such as “Unknown” or “-”. It does not need to represent a real category; it simply prevents the pivot from receiving a NULL value, which should allow it to render the month correctly. Now I wasn’t able to reproduce your exact environment, so results may vary slightly. Let me know how it goes!
Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.
If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.
