Forum Discussion
Solved
Weekly numerical data spread over two months, how does PivotTable calculate the difference?
Scenario:
Amount of stock is calculated on a weekly basis, these numbers are inputted into excel. Weeks start on Monday, therefore for August 2022 we have the following data rows:
8/1/22
8/8/22
8/15/22
8/22/22
8/29/22
I want to find the monthly sum of the data inputted into these rows, however, the PivotTable assumes that all data in the 8/29/22 row is from the month of August - the problem is 60% of the data in this row is from August, the remaining 40% is from September.
Is there any way to have PivotTable automatically calculate these differences?
I am actually referring to "documents received". I used "stock" to generalize the question, but I am looking to keep track of the amount of documents we receive.
So this is a good time to realize that words are important. "Amount of stock," the description in your original opening sentence, sounds more like inventory levels of stock--whether that stock is documents or eggs. "Documents received" is entirely different. So you're counting not "documents in inventory" but rather new/incoming documents each week.
And on that basis, SUM makes a lot of sense.
(By the way, I'm not a statistician either; just a person who pays a lot (some, like my wife, would say "an annoying amount") of attention to the precise use of words. So I apologize in advance for making too big a deal out of word use....)
I've been keeping weekly stats for a couple years and was recently asked to switch to a monthly format. I want to retain the data I have previously collected while being able to compare with the new monthly data I will be collecting.
So much here--in response to that request to go from weekly to monthly--is going to depend on the exact situation. (Just as words themselves can matter a lot, so can circumstances.)
It's important to realize that even if it were suggested that you should go back in history and take every week that crossed the boundaries of a month and apportion the number of incoming documents according to the ratios of days of the week that fell in each month, you would still be retroactively just coming up with a "reasonable approximation." Only if you went back and got the actual days each document came in on those month-straddling weeks would you be being really precise.
So it's important to assess the degree of precision that's really needed. Are you, for example, in a setting where absolute precision is called for in this count, with potential legal consequences for being inaccurate? Or is this case of, say, the incoming documents consisting of letters of gratitude for a product, or complaints regarding product defects. In the latter case, it's not the precise number that matters; rather it's the trends. Point #1, therefore: it matters a lot whether you are being expected to track these for precision vs highlight trends.
Point #2 has to do with data entry. You say the data are entered into Excel weekly, but don't say when in the week that entry takes place. Is it daily, as documents are received? Or at the end of the week? Or scattered very much on an "as convenient" basis? Whatever it has been, going forward, I'd suggest you be very systematic about entering the data, according to the following scheme:
- enter a week's data at the end of each week for any week that is fully in one month
- enter a few days (whatever is called for) at the end of each month that falls in the middle of the week
- enter the date of the data entry itself as the date associated with the data (i.e., not the Monday that started the week, but presumably the Friday date, if you use the "standard" five day work week. That way the date reflects documents received in the period ending on Date of Entry
So, to go back to the set of dates in your first post, your entries for August would carry different dates
The counts would be the same in the first four rows, but different in the last two.
Pivot Table's "normal way" of consolidating then would, going forward, be giving accurate accounts by month.
But that leaves the question of how do you deal with all that past data to compare August 2021 with August 2022, and so forth, for all the other months. Back up to Point #1, and assuming your situation falls more toward the "tracking trend" need, I would talk with those requesting the data and describe the issues we've talked about. My own recommendation would be to just let Pivot Table do its thing, consolidating the data (SUM is fine, now that we know what we're talking about) on a monthly basis, and if it seems to warrant it...add a footnote to the report that says something to the effect:
"Prior to __/__/2022 we collected this data on a weekly basis; since then we've done it monthly, so there are minor differences in how the data were collected on weeks that crossed month boundaries, but they are deemed insignificant for the purposes of this trend analysis."
That's probably too wordy, but you get the idea. Basically, unless there are legal ramifications, I don't think there's a legitimate need to go back and re-characterize all of the old weekly data.
Hope that makes sense. Thanks for taking the time to look at my question!
Hope that makes sense. Thanks for taking the time to look at my answers!
4 Replies
From a purely conceptual point of view, I need to ask a couple questions here, if you don't mind.
- You say you enter weekly (on Mondays) the amount of stock. [You don't way what kind of stock we're actually talking about, so I'm going to assume you're referring to stuff on a shelf in a hardware store or grocery store, or some other kind of commercial establishment.]
- Presumably you do this by each category or specific type of stock.
- You say you want to use Pivot Table to get the SUM of all those entries for each month. That surprises me! So here's question #1. Why would you want the sum, rather than, say, the weekly average for each item for each month? That would seem to me to be more meaningful, answering the question does stock rise or fall each month? Is there a seasonal cycle to it?
And the final question: your question seems to be seeking a precise monthly cutoff. The data isn't collected in a way that readily does that (and I am pretty sure Pivot Tables don't interpolate quantities based on mid-week monthly ends and starts when the data collected differentiates by week rather than by month). So Question #2. Does it really matter in the big picture anyway?
- You say you enter weekly (on Mondays) the amount of stock. [You don't way what kind of stock we're actually talking about, so I'm going to assume you're referring to stuff on a shelf in a hardware store or grocery store, or some other kind of commercial establishment.]
I am actually referring to "documents received". I used "stock" to generalize the question, but I am looking to keep track of the amount of documents we receive. - Presumably you do this by each category or specific type of stock.
Yes, there are a few different categories of documents, on different worksheets. - You say you want to use Pivot Table to get the SUM of all those entries for each month. That surprises me! So here's question #1. Why would you want the sum, rather than, say, the weekly average for each item for each month? That would seem to me to be more meaningful, answering the question does stock rise or fall each month? Is there a seasonal cycle to it?
I want the sum because I am curious about the total amount of documents received, I would like to compare this to previous months to gain insights on any trends or issues. I'm not really a statistician - would "averages" get me to the same place?
And the final question: your question seems to be seeking a precise monthly cutoff. The data isn't collected in a way that readily does that (and I am pretty sure Pivot Tables don't interpolate quantities based on mid-week monthly ends and starts when the data collected differentiates by week rather than by month). So Question #2. Does it really matter in the big picture anyway?
Hmmm, as I said, I'm not a statistician. I've been keeping weekly stats for a couple years and was recently asked to switch to a monthly format. I want to retain the data I have previously collected while being able to compare with the new monthly data I will be collecting. Hope that makes sense. Thanks for taking the time to look at my question!I am actually referring to "documents received". I used "stock" to generalize the question, but I am looking to keep track of the amount of documents we receive.
So this is a good time to realize that words are important. "Amount of stock," the description in your original opening sentence, sounds more like inventory levels of stock--whether that stock is documents or eggs. "Documents received" is entirely different. So you're counting not "documents in inventory" but rather new/incoming documents each week.
And on that basis, SUM makes a lot of sense.
(By the way, I'm not a statistician either; just a person who pays a lot (some, like my wife, would say "an annoying amount") of attention to the precise use of words. So I apologize in advance for making too big a deal out of word use....)
I've been keeping weekly stats for a couple years and was recently asked to switch to a monthly format. I want to retain the data I have previously collected while being able to compare with the new monthly data I will be collecting.
So much here--in response to that request to go from weekly to monthly--is going to depend on the exact situation. (Just as words themselves can matter a lot, so can circumstances.)
It's important to realize that even if it were suggested that you should go back in history and take every week that crossed the boundaries of a month and apportion the number of incoming documents according to the ratios of days of the week that fell in each month, you would still be retroactively just coming up with a "reasonable approximation." Only if you went back and got the actual days each document came in on those month-straddling weeks would you be being really precise.
So it's important to assess the degree of precision that's really needed. Are you, for example, in a setting where absolute precision is called for in this count, with potential legal consequences for being inaccurate? Or is this case of, say, the incoming documents consisting of letters of gratitude for a product, or complaints regarding product defects. In the latter case, it's not the precise number that matters; rather it's the trends. Point #1, therefore: it matters a lot whether you are being expected to track these for precision vs highlight trends.
Point #2 has to do with data entry. You say the data are entered into Excel weekly, but don't say when in the week that entry takes place. Is it daily, as documents are received? Or at the end of the week? Or scattered very much on an "as convenient" basis? Whatever it has been, going forward, I'd suggest you be very systematic about entering the data, according to the following scheme:
- enter a week's data at the end of each week for any week that is fully in one month
- enter a few days (whatever is called for) at the end of each month that falls in the middle of the week
- enter the date of the data entry itself as the date associated with the data (i.e., not the Monday that started the week, but presumably the Friday date, if you use the "standard" five day work week. That way the date reflects documents received in the period ending on Date of Entry
So, to go back to the set of dates in your first post, your entries for August would carry different dates
The counts would be the same in the first four rows, but different in the last two.
Pivot Table's "normal way" of consolidating then would, going forward, be giving accurate accounts by month.
But that leaves the question of how do you deal with all that past data to compare August 2021 with August 2022, and so forth, for all the other months. Back up to Point #1, and assuming your situation falls more toward the "tracking trend" need, I would talk with those requesting the data and describe the issues we've talked about. My own recommendation would be to just let Pivot Table do its thing, consolidating the data (SUM is fine, now that we know what we're talking about) on a monthly basis, and if it seems to warrant it...add a footnote to the report that says something to the effect:
"Prior to __/__/2022 we collected this data on a weekly basis; since then we've done it monthly, so there are minor differences in how the data were collected on weeks that crossed month boundaries, but they are deemed insignificant for the purposes of this trend analysis."
That's probably too wordy, but you get the idea. Basically, unless there are legal ramifications, I don't think there's a legitimate need to go back and re-characterize all of the old weekly data.
Hope that makes sense. Thanks for taking the time to look at my question!
Hope that makes sense. Thanks for taking the time to look at my answers!
- You say you enter weekly (on Mondays) the amount of stock. [You don't way what kind of stock we're actually talking about, so I'm going to assume you're referring to stuff on a shelf in a hardware store or grocery store, or some other kind of commercial establishment.]
