Loading

Adjusting QOQ Growth Calculation when the Most Recent Quarter is Incomplete

Udgivelsesdato: Apr 30, 2025
Opgave
How to adjust a quarter over quarter (QOQ) growth calculation to only compare completed months.

For example, if 2017 Q1 is being compared to 2016 Q1, but 2017 Q1 only has two months of data so far, then the QOQ percentage will be lower than expected because data is missing for 2017 Q1. To correct for this, we can only use data for the first two months of both Q1 2017 and Q1 2016.
Trin
The attached example workbook uses Superstore sample data to demonstrate the following directions:
  1. Drag [Order Date] to Rows.
  2. Click the plus sign on YEAR(Order Date)  to add QUARTER(Order Date) to Rows.
  3. Drag [Sales] to Text.
  4. Right-click [Sales] on the Marks card and select Quick Table Calculation > Percent Difference.

    By default, the percent difference will be between one quarter and the quarter chronologically before it, e.g. Q1 2017 and Q4 2016. For a QOQ calculation we want to compare the same quarter between years, e.g. Q1 2016 and Q1 2016.

  5. Right-click [Sales] on the Marks card and select Edit Table Calculation.
  6. In the Table Calculation dialog, do the following and close the dialog:
    • Select Specific Dimensions
    • Drag Quarter of Order Date before Year of Order Date
  7. Create a calculated field with a name like "Sales excluding incomplete months" and a calculation similar to the following:
    IF NOT (
    YEAR( [Order Date] ) = YEAR( { FIXED : MAX( [Order Date] ) } ) - 1
    AND DATEPART( 'quarter', [Order Date] ) = DATEPART( 'quarter', { FIXED : MAX( [Order Date] ) } )
    AND MONTH( [Order Date] ) > MONTH( { FIXED : MAX( [Order Date] ) } )
    )
    THEN [Sales]
    END
     

    The calculation will return sales for everything except for any months in the second most recent year in the data set that are missing from the most recent quarter.

  8. Select Analysis > Create Calculated Field and drag SUM(Sales) with the quick table calculation applied into the calculation window. Tableau Desktop will automatically write out the formula used for the percent difference. This calculation will look similar to:
    (ZN(SUM([Sales]))
     - LOOKUP(ZN(SUM([Sales])), -1))
    / ABS(LOOKUP(ZN(SUM([Sales])), -1))
  9. Modify the Tableau generated percent difference formula to replace [Sales] with [Sales excluding incomplete months] for any looked up months. The final calculation may look like:
    (ZN(SUM([Sales]))
    - LOOKUP(ZN(SUM([Sales excluding incomplete months])), -1))
    / ABS(LOOKUP(ZN(SUM([Sales excluding incomplete months])), -1))
     

    In the above calculation, only the sales value for the previous date period is replaced. This means that the current quarter will always use the full sales value, but it will be compared to the filtered sales if that quarter is filtered.

  10. Name the calculation and click OK. In this example, the calculation is named "% Difference Sales"
  11. Replace SUM(Sales) with the quick table calculation with [% Difference Sales]
  12. Right-click [% Difference Sales] on the marks card and select Edit Table Calculation
  13. In the Table Calculation dialog, do the following and close the dialog:
    • Select Specific Dimensions
    • Drag Quarter of Order Date before Year of Order Date
Vidensartikelnummer

001458093

Vedhæftede filer

adjusted QOQ.twbx

866 KB

 
Indlæser
Salesforce Help | Article