How leveraging simple math tricks can dramatically increase the accuracy of your Quarter to Date (QTD) versus Previous/Prior Quarter to Date (PQTD) comparisons.
Comparing QTD to PQTD is often a powerful way of performing an apples-to-apples comparison when analyzing a myriad of different types of data. This is especially true when dealing with sales data. Why? Because sales departments often view the world in terms of quarters, whether for their own internal goals/benchmarks or because of public companies' need to report quarterly earnings. It's extremely useful to be able to ask, at any particular point in a quarter, "am I doing better than I was at the same time last quarter, or worse?" Knowing the answer to this question in both a timely manner and with a high degree of confidence can mean the difference between finishing the current quarter strongly or not.
How can you be sure that the analysis that you are performing is truly valid though? The key is, of course, to eliminate variations between QTD and PQTD; to try to make the previous quarter-to-date as similar to the current quarter-to-date as it is possible to make it. This article will focus on the single most important variable affecting validity: the number of days in each comparison period. If the number of days in PQTD is substantially different than the number of days in QTD, then the analysis can potentially be worse than useless, it can be misleading. The way you control this variable depends on the granularity of your data. Some sales data is available at a daily grain. Other data (most often data that is purchased from third party vendors) is available at other grains: monthly, weekly, or "split week."
A quick note: I usually like to avoid picking fights in my blog. That said, the reason I am referring to the methods discussed below as the "correct" way (them's fighting words!) to perform QTD/PQTD calculations is because, simply, they are (with the possible exception of the "Weekly Data" section, which is open to reasonable debate). Read on, and you'll see that the math doesn't lie.
Monthly or Daily Data
This is the simplest type of data to work with for purposes of quarter comparisons. Just count the number of days/months that you have for the current quarter (QTD), and take the same number of days or months in the previous quarter for your PQTD numbers. For instance, if we're dealing with monthly data and have just received November 2017 data, then the PQTD period is July - August 2017 (2 months into each quarter). Likewise, if we have daily data and just closed out November 15, 2017, then the PQTD period is July 1, 2017 - August 15, 2017 (46 days into each quarter).
Split Week Data
This is where the math gets really interesting. The only place I've ever seen split week data is in the pharmaceutical industry, where it's the standard for reporting sales at many organizations in the United States. By the way, if you happen to know of any other industries where split week is used, please share in the comments, I would love to know! Split week data is weekly data that is simply split into two unequal half-weeks when a week crosses a month boundary. For instance, if your weeks end on Fridays and July 31st happens to be a Monday, you would have two "split weeks" of data: (1) the 3-day "split week" starting on Saturday, July 29, and ending on Monday, July 31, and (2) the 4-day "split week" starting on Tuesday, August 1, and ending on Friday, August 4. Split week data is, consequently, a useful vehicle for reporting sales at both weekly and monthly levels. The correct method to calculate the PQTD period when you have split week data is:
- Count how many days into the current quarter the most recent week-ending date is
- Add the same number of days to the previous quarter start
- Round up or down to the nearest week-ending date (capped by the previous quarter start and end dates)
If you use the above method, you are mathematically guaranteed that the PQTD period will either be exactly the same number of days into the previous quarter as QTD is to the current quarter, or off by only 1 day. For example, let's say that the latest week-ending date for your QTD calculations is Friday, May 11, 2018. Step 1: this day is 41 days into Q2 2018 (Apr - Jun). Step 2: Adding 41 days to Q1 2018, we get Saturday, February 10, 2018. Step 3: rounding to the nearest week-ending date (Friday), we end up with Friday, February 9, 2018. This day is 40 days into Q1, which yields a difference between QTD and PQTD period lengths of only 1 day.
Why does this work? The rounding in step 3 is really the key. Every calendar quarter is going to be 90, 91, or 92 days. Since 91 is a number that is evenly divisible by 7, the number of days in a quarter divided by 7 will have a remainder of 0, 1, or 6. What this tells us is that each quarter starts on either the same weekday as a quarter that neighbors it, or a single day earlier/later. Because we allow rounding either up or down in step 3, a remainder of 6 is really the same thing as a remainder of -1.
There is one additional step that's necessary after step 3. Very, very rarely (0.2% of the time), Q2 (Apr - Jun) starts on a week-ending date (e.g. Friday) and Q1 (Jan - Mar) starts on a day after a week-ending date (e.g. Saturday). This happens every year (except for leap years) that April 1st is a week-ending date. In this one special situation, if you are looking at a QTD period of 1 day (Apr 1), the above logic will return a PQTD period of 6 days (Jan 1 - 7). This is because the algorithm is set to never go earlier than the start of a quarter. What we want to do in this case, is force the PQTD period to be zero days, which would give us a much closer comparison to a 1-day QTD period. Fun factoid: if your weeks end on Fridays, the last time this happened was in 2011 and the next time it will happen will be in 2022.
Many companies do themselves a disservice by either forgetting (a) that rounding down is an option, or (b) the final step I just mentioned (that 0 days is preferable to 6, when compared to 1 day in the current quarter). In either case, you can end up with differences in the lengths of your comparison periods of as much as 500%, something that can clearly lead to wrong conclusions in a QTD vs PQTD analysis.
For a more in-depth look at this analysis (with as many examples as you would care to generate), including either UI or data model code (whichever you prefer) that you can plug in to your own QlikView applications, download the following app:
Because traditional weekly data (as opposed to split week) routinely crosses month and quarter boundaries, this type of data does not lend itself neatly to QTD comparisons. My honest advice would, therefore, be to try to avoid traditional QTD comparisons altogether. If you absolutely must, you will be forced to either chop up your weeks into quasi-split weeks using some sort of algorithm that is appropriate for your particular sales patterns (for instance, ~14% of total weekly data attributed to each day) or else take a massive hit on the validity of your comparison by intentionally comparing periods that you know are not really that close to being equal. Either option is a bad idea, in my opinion.
I would instead recommend that you redefine what a "quarter" means, and ignore all real calendar month, quarter, and year boundaries. The simplest way to do so is to say that a quarter = 13 weeks. Let's say your weeks end on a Friday. Taking 2017 as an example, your 4 "quarters" would be:
- Start: Week starting 12/31/2016 and ending 1/6/2017
- End: Week starting 3/25/2017 and ending 3/31/2017
- Start: Week starting 4/1/2017 and ending 4/7/2017
- End: Week starting 6/24/2017 and ending 6/30/2017
- Start: Week starting 7/1/2017 and ending 7/8/2017
- End: Week starting 9/23/2017 and ending 9/29/2017
- Start: Week starting 9/30/2017 and ending 10/6/2017
- End: Week starting 12/23/2017 and ending 12/29/2017
As you can see, the result is that the 13-week pseudo-quarters roughly follow the calendar quarters. The big upside to this method is that you can always perform a QTD versus PQTD comparison with 100% parity in comparison period lengths. The downside, of course, is that the "quarters" aren't real. So if you need to perform any sort of fiscal quarterly actual/projected revenue reporting, you would not be able to do so. Of course, since there is no mathematically accurate way to get true quarterly reporting from weekly data in the first place, it's highly unlikely that this type of data is the source for any type of official financial reports anyway.
Thanks for reading and, as always, happy Qliking!