Forum Discussion
Missing values in pivot table
Riny_van_EekelenI have been having the same issue. I have a data table with various date/text columns and an amount column. Where there is an entry with two duplicates either in the date, the text or the amount row, these sometimes appear with blank data or missing entirely. I have included screenshots of the data table, the pivot table with the data missing and one with the text amended to make the data show. This is totally impractical for the main pivot tables as there are duplicate monthly amounts for various accounts and I am having to amend each entry in the data table to ensure they show.
I have tried to change the field settings for "Repeat item lables" for the whole table in the "Design" Tab, but this doesn't work.
Windward On the Report Layout, first select Tabular, then Repeat item Labels. Does that help?
- This worked for me. Thank you!
- To help other users find solution to your post please don't forget to mark it as answered. Thank you
Riny_van_Eekelen Sorry. These are already ticked in the field settings box. I have tried this in the design/report layout tab for the whole table, but this just shows the year and week duplicated (which I don't want) and not the missing values. I have attached a copy of the workbook so you can see.
For these two fields setting Repeat item label is not set.
If only for one field to set
I didn't catch what an idea of such PivotTable which aggregates nothing, in attached file it's kept as it is with only one field changed.
SergeiBaklan I don't want the "Year", "Week No." or "Date" Columns to include "Repeat data in rows" as this is difficult to read with multiple date values in a large table. The attached workbook is an abridged version only.
Windward By the way, what an awkward pivot table, using the Total Amount field as one of the row headers.
Windward I opened your file, chose Tabular first then Repeat.
All columns are "filled". See attached.
Hi again. I have already tried your suggestion and in your illustration the pivot table is actually missing two entries of £78.91 on 9/11/21 for Igloo Energy. You can see in the "transactions" data there are 4 entries on the same date. Two of these are the missing ones as there is both duplicate text and a duplicate amount. If I change the text in some small way they appear.
The column "Total Amount" is actually mis-labelled and should just be "Amount" as it is not a total or aggregated data. Again this can be seen in the tab "Transactions" Data in my example.
