SQL Get Quarter over Quarter values

Question

I'd Like to get QoQ from a dataset with Q3 and Q4 data that also has a report date column, each row should have a QoQ value for each fiscal month (represented by a report date), Q4 should compare against Q3 but my statement only seems to be comparing within the same quarter i.e. Q4 is comparing against Q4 instead of Q4 comparing to Q3 ..

I am using the lag function but not sure what I am doing wrong if someone could please see code below.

 SELECT [Year], 
  [SalesDate] as Report_Date,
       [Quarter], 
       Sales,
     
       LAG(Sales,  1, 0) OVER(
       PARTITION BY [Year] ,[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) AS [QuarterSales_Offset],
                sales - LAG(Sales) OVER(
       PARTITION BY [Year] ,[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) as diff,
Case When 
LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) = 0 then null else

(
sales - LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC))/ LAG(Sales,1,0) OVER(
       PARTITION BY [Year],[Quarter]
       ORDER BY [Year], 
                [Quarter],
                salesDate
                ASC) end as QoQ
FROM dbo.ProductSales_2;

Query Output:

Tag the right database. You have not told about the problem you are facing. — Sujitmohanty30, Oct 10 '20 at 19:08

Parfait · Accepted Answer · 2020-10-17T15:04:46.207

Since LAG() at 1 offset returns previous row and your data is at month level, you actually compare month over month in each quarter. Consider a different approach such as joining two subsets of your data by quarter and month in quarter.

QuarterMonth column can be calculated with ROW_NUMBER() expression (i.e., running count of months within each quarter). Since month gaps in sales data can potentially arise, use a year_quarter_month calendar table aligned to your fiscal year. Altogether, this allows comparison of first FY Q4 month (2020-08-31) to first FY Q3 month (2020-05-31) by columns.

WITH unit AS (
   SELECT yqm.[Year]
        , yqm.[Quarter]
        , yqm.[Month]
        , COALESCE(p.[Report_Date], DATEADD(DAY, -1, DATEFROMPARTS(yqm.[Year], yqm.[Month]+1, 1))) AS [Report_Date]
        , p.[Sales]
   FROM year_quarter_month_table yqm
   LEFT JOIN dbo.ProductSales_2 p
      ON yq.[Year] = p.[Year]
      AND yq.[Quarter] = p.[Quarter]
      AND yq.[Month] = p.[Month]

), sub AS (
   SELECT [Year]
        , [Quarter]
        , [Month]
        , ROW_NUMBER() OVER(PARTITION BY [Year], [Quarter] 
                            ORDER BY [Report_Date]) AS [QuarterMonth]
        , [Report_Date]
        , [Sales]
   FROM unit
 )

SELECT q4.[Year]
     , q4.[Report_Date] AS Q4_Date
     , q4.[Sales] AS Q4_Sales
     , q3.[Report_Date] AS Q3_Date
     , q3.[Sales] AS Q3_Sales
     , q4.[Sales] - q3.[Sales] AS Diff
     , COALESCE((q4.[Sales] - q3.[Sales]) / q3.[Sales], 0) AS QoQ
FROM sub q4
LEFT JOIN sub q3
   ON  q4.[Year] = q3.[Year]
   AND q4.[Quarter] = 4
   AND q3.[Quarter] = 3
   AND q4.[QuarterMonth] = q3.[QuarterMonth]

You may be able to generalize to any quarter-over-quarter calculation and not just Q3 and Q4:

WITH sub AS (
  -- SAME CTEs AS ABOVE
)

SELECT curr_qtr.[Year]
     , curr_qtr.[Report_Date] AS Curr_Qtr_Date
     , curr_qtr.[Sales] AS Curr_Qtr_Sales
     , last_qtr.[Report_Date] AS Last_Qtr_Date
     , last_qtr.[Sales] AS Last_Qtr_Sales
     , curr_qtr.[Sales] - last_qtr.[Sales] AS Diff
     , COALESCE((curr_qtr.[Sales] - last_qtr.[Sales]) / last_qtr.[Sales], 0) AS QoQ
FROM sub curr_qtr
LEFT JOIN sub last_qtr
   ON  curr_qtr.[Year] = last_qtr.[Year]  -- ASSUMING FISCAL YEAR AND NOT CALENDAR YEAR
   AND curr_qtr.[Quarter] = last_qtr.[Quarter] + 1
   AND curr_qtr.[QuarterMonth] = last_qtr.[QuarterMonth]

This solution works quite well in scenarios where the Q has all the months present, so F-Q3 would have May, June and July present, however, doesn't seem to work when a month is missing in the previous/last quarter say only July present in dataset for F-Q3 working to see if I can manipulate your above code but any additional input would be appreciated — Dan A, Oct 16 '20 at 20:33
As I mentioned, this solution is *assuming no missing month in every quarter*. To fill in gaps, add a first CTE that left joins a dummy year/quarter/month calendar table and second CTE is based on first fill-in gapped table. See edit. `REPORT_DATE` is filled in using various date functions to return last day of month. There are many ways to build a *calendar table* (with recursive CTE, via application code like Python, or your basic Excel). Search this term online. — Parfait, Oct 16 '20 at 21:45
Yes, you are correct I missed that caveat on missing months in your previous comments ... I have now proceeded as you suggested ... I created a Fiscal Calendar to fill in the gaps and output looks as I expected it to be. Much appreciated. — Dan A, Oct 17 '20 at 14:38

SQL Get Quarter over Quarter values

1 Answers1