Forum Discussion
Last 12 months counting
I'm not following your description. Maybe it's the "trees" vs the "forest" kind of issue. I'm looking for the higher level definition. To me a rolling twelve months would not care whether there was a zero in any one of those twelve months. That would be that month's value. Period.
You seem to be saying something along the lines of "I only want 12 months of values other than zero," but that wouldn't be a "rolling twelve months" (in my dictionary); that would be labeled something else.
So what we're looking for, in order to write a formula, is a plain english definition of what YOU mean by what you've been referring to as "rolling twelve months" [but perhaps needs a more accurate or precise label].
Each row represents a person. I only want to count each person once in any 12 month period. If they were counted in Jan22, they can't be counted again until the following Jan. I have a problem in row 2 whereby they are counted once in Jan 22 but not again (by using my formula). By using your formula, you are counting them every time they appear. I need a unique count for the last 12 months.
I've been taking your very first sentence:
I am trying to produce a rolling 12 month count sheet
to mean that you want a rolling 12 month total (which is how that term is usually used).
So I'm still not really clear. What is being counted? The appearance of a number other than zero in any of the 12 months? So regardless of what the number is, it's either one or zero as far as the 12 month count is concerned? Or is it the total of the numbers in a contiguous 12 month period for each row. Or something else.
The word "unique" is ambiguous in this context. Are saying you're looking, in effect, for "did this person appear once in the preceding 12 months"? So maybe what should be appearing is a simple "Yes" or "No" referring to the previous 12 month period(?) Whether or not that's the case, you seem to want a binary outcome, 1 or 0 OR Yes or No.
- Ah I see. I understand the confusion now- your summary is exactly it. I want a binary- yes I am counting you this month or not. The rolling 12 month was referring to always being the previous 12 months. Thanks for bearing with
There's still some ambiguity, though. (Sorry: this is why I was looking for a clear definition from the very start; and, yes, I can be very finicky about what I mean by clear definitions).
Do you want to know the number of months that a person appeared during the preceding 12 months, or only that they appeared at least once during the preceding 12 months.
In other words, is it
- binary for 12 months, so your 12 month summary will be either 1 or zero, Yes or No; ,
or
- binary for each of the preceding 12 months, so the rolling 12 months could be any number up to 12.