Search code examples
sqlfiscal

SQL Get Quarter over Quarter values

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:

enter image description here

Solution

  • 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]