This pivot table is displaying 5 category columns in the QS GUI:
There are actually 7 columns, which can be seen when you download to Excel:
The “Role” field is a calculated field which helps me to sort the columns in a custom order, but I get the same results if I use the underlying field from the dataset.
Any ideas what is limiting the number of columns?
Hi @Jeremy_Likezero,
we probably need to have more info about how the Pivot is configured.
Let’s start with these info for troubleshooting the issue 
Andrea
Thanks.
There is no RLS.
If I remove all the filters and the controls, the situation is unchanged. 5 pivoted columns in the gui; 7 in the exported xlsx. The only config options I changed was Hierarchy → Tabular, hide +/-, column width 140 pxl, subtotals off, totals on.
If I rebuild the graphic from scratch, the point at which the columns mess up varies, but is most consistent when I put a calculated “Role” field in column 1. But, here in the screenshot, it is 3 simple fields.
@Jeremy_Likezero are you doing the export from the Analysis window or from the published Dashboard?
I was trying to replicate the issue, but I do not find any combination that recreates it.
You also already tried with the not-calculated field, so also that one is not the issue it seems.
My best guesses here were on RLS or, if you’re exporting using schedules from a public Dashboard, you also have this config than can alter your download:
I assume, but confirm it to me please, that all the columns have at least one valid value, right?
Andrea
I am downloading from the Analysis designer.
Every column has counts in it, even the one with a NULL Role.
I’m thinking this is (another) good, old-fashioned QS bug!
I have found out that as I scroll down, more columns become visible as the data is encountered. Maybe that is a design “feature”. It means, of course, that the user would have to go to the “Controls” in order to know what the set of columns is. I can see how QS night have to limit the number of columns, but I would have thought that 20-30 columns ought to be a reasonable number for most pivot operations. 5 is a bit useless for most category analyses.
I have raised a case with AWS. In the meantime, I’ll see what I can do to create a dummy record that sorts to the top with zeroes in it. Maybe it doesn’t like NULLs in the “empty” cells…
That didn’t seem to work. If this behaviour is by design, then it renders the Grand Total line invalid! The displayed column totals do not add up to the grand total. Embarassing!
As you were … It is the NULLs that the pivot table doesn’t like. Ok. The default behaviour is quite clever if you have very many categories and are not worried about having a grand total that adds up to the totals of the displayed columns. If you want all the columns, you have to have all the row/column coordinates in your dataset.
This dataset is driven by a crosstab of the Agreement Id and Entity Role. The “real” data is left-joined to the xtab, with the result that all the row/column coordinates exist in the dataset, even if every other field is NULL.
The next step (for me) is to reduce the xtab to those coordinates only that exist somewhere in the dataset.
