For reference I have a similar problem to the follow thread, with a few alterations, making this solution not work as well as I would want.
The problem is work related, but I have created the following example to mask the real problem (sry, had to).
Example problem: I write books, and get yearly number of sales per book. Sometimes however, I get an updated value for sales for a given year, which might be long after the first entry.
The data is originally flat in one table, but I have (so far) decided to split this into to tables, one for listing the books, and one for listing the sales. This is to facilitate that some information about a book is static and not dependent on year and does not need to ever repeat, and to avoid having to "expand" the books table with a new column every year, creating a lot of empty cells for books that do not sell anything or have changed values in a given year. (note my real problem is much larger than the example).
Old Flat Table This is not completely filled in, but just to give you an idea. 3 times each year i get updated sales amounts. How this has been done until now is that on each update the entire existing list of books is copied down, and updated values are put in. I do not like this way of doing it for several reasons:
- Store more data than necessary. Titles are repeated and sales figures are added in even where no changes exist.
- To create a pivot table of this each year is added separately into the value field instead of using years as a column thing
I am therefore considering splitting it into 2 tables as such (with a reference on book id between the tables):
The date field in this table is the date the updated sales values where added.
What I need to create is pivot tables that show:
- The number total sales of each book each year using the latest updated value
- The differense in sales of all books all years compared to last time it was reported. That is, if the sales report last time was correct it should show 0, if it has been altered it should show by how much.
Point 2. can be solved creating 2 versions of 1., one for the current time and one for the last reported time, and taking the difference between them. When trying to do 1 in pivot, the problem is similar to the one linked above, but different in one way: I do not intend to have a line for a book for a year if the sales amount was originally correct. Hence, if I use the filter on max of date as in the example, sales for books that were not corrected on that max date show 0 sales.
Here, if the sales for a given year is not updated on the latest update date for that book, it shows 0. Here one can also see that as no changes where done to sales in years 2020, 2021 and 2022 for any of the books, those values are completely ommited.
What would be the best way to solve what I intend to solve, if you can understand it from this description?
Should I make sure that I add a line for each book each year each time i put in updates (would solve it but i find it undesireable).
Should I only keep the current value, and store historic sales in a seperate table, and if I do that, how do I create pivot table 2 (that which shows the difference in sales from one reporting period to the next).