Pivot update

At reference 1, I reported difficulties with including rows for which there was no data in a (Microsoft Excel) pivot table, the rows in questions being days or dates.

Today, I had another go. First, I included an additional row for the date in question, March 30th, with shift scored as minus 1. This resulted in a zero row for that day in the pivot table. But a little clumsy, using two rows in place of none. Second, I included an extra column called 'records', which always took the value 1. Then for March 30th all the data columns were set to zero. Again, this resulted in a zero row for that day in the pivot table, as shown in the snap top left. And using two rows in place of none seems better than using two.

All this arises because of Microsoft's concern to make the production of tables as easy as possible. The user has to do little more than nominate the rows and columns. Specialist tabulators, in which I once took a professional interest, put rather more work on their users, requiring them to specify the various dimensions of a cross tabulation as entities in their own right, rather than their being by-products of analysing the data. An approach which, as well as side-stepping the zero row problem, allows the inclusion of a profusion of sub-totals, should that be desired, as it often is when, for example, the dimension corresponds to a data item which can take many values, such as area or cause of death. And some of the better tabulators can even have a stab at data items which can make several values for just one record - which would enable, in our present difficulties, useful work to be done with multiple causes of death.

To be fair to Microsoft, they do have a stab at sub-totals. They know that date is a date field, from which month can be extracted, so they offer monthly sub-total, which one gets by ticking the months box in the large panel, top right in the snap above.

Reference 1: https://psmv4.blogspot.com/2020/04/pivoting-bricks.html.