Creating a Year over Year (YOY) Comparison

In the example below we will be comparing the sales for 2017 with that of 2016. The end result will look as follows:

The problem that we normally have when doing year over year comparisons is that the values in our database is stored in one column – we don’t have a column for 2017 sales and another one for 2016’s sales.

Filtering this report to only show the invoices for 2017 and 2016 will also not solve our problem as we will still have only one column with the invoiced amount. We need a way of splitting the sales into two separate columns.

This can be achieved using Append Sub-Queries in Yellowfin. We use Append Sub-Queries when you want to add a column to your report that requires filters different to the rest of the report.

In the example below, we will create three queries: a master query with our common field, Athlete Region, that will be used to link to the other queries and two sub-queries, one for each year’s sales by region. Each of these sub-queries will be filtered to only show the sales for that specific year.

Master Query

The master query will contain only our common value. In this example, the common value between the two years are the Athlete Regions they are broken down into, and this will act as our joining field.

1. Click on Create to begin building a report using the Ski Team view.

2. Add the Athlete Region field to the Columns area.

2017 Sales Sub-Query

This query will include the Athlete Region field which will be used to join to the master query and the invoiced amount.

3. Click on the + Sub Query link and add a Basic Append.

   We use an Append as we wish to add columns to the Master Query. This Append will be used to return a column for the Sales in 2017.

   

   
   

   

4. Ensure the join type is Left Outer Join and select Athlete Region from the Master Query Fields dropdown. Drag the Athlete Region field to the Sub-Query Fields area. We added the Athlete Region field in order to create the second half of our join to the Master Query. This field will only be displayed once in the final report output.

   

5. Add the Year (Dim) field to the Sub Query Filters area. The Year (Dim) field will be used to restrict the results to 2017.

   

6. Name the Sub-Query “2017 Sales”. Naming the sub query is useful when we get to the Filters step and formatting on the preview page. Instead of just having multiple Append labels, each Sub Query will have a relevant label to help identify it.

   

7. Click on Save.

8. Add Invoiced Amount to the Column area.

   

   You will notice that the Athlete Region field is grey, this is because we are currently working in the sub query, rather than the master query. Whichever fields are located in the active query are blue (in this case Invoiced Amount).

   

2016 Sales Sub-Query

9. Click on the + Sub Query to add another Append Sub-query. This query will be filtered to only show the sales for 2016.

10. Create the same Sub Query as above, but name it “2016 Sales”. Ensure that this sub query has the same fields, filter, and join as the “2017 Sales” sub query.

    

11. Add Invoiced Amount to the Column area.

    

Filtering the Sub-Queries

Both of the invoiced amount columns will show the same results. Even though we added the Year (Dim) filters to both of the sub-queries we now need to apply the relevant filters.

12. Click on the filter Advanced Settings link.

    You will now notice that the Report Filter page looks different, using a tab for each query. The tabs will reflect the names we gave them during the sub query building process.

    

13. Drag the Year (Dim) field into the Master Query filters area twice. Change the logic between the two filters to OR. Set the filters to 2017 and 2016 respectively.

    This is going to make sure you have Regions displayed that had invoices for 2017 or 2016, ensuring you get all the data you need.

    

14. Click on the “2017 Sales” tab and set the filter to 2017.

    This will mean that anything in the “2017 Sales” query will be within the 2017 Year.

    

15. Click on the “2016 Sales” tab and set the filter to 2016.

    This is the filter that will be used to ensure the 2016 Sales column only reflects the data for 2016.

    

16. Close the Filter Settings.

    The Invoiced columns will now have different values.

    

17. Change the invoiced amount columns to “2017 Sales” and “2016 Sales” respectively.

    

Calculating a Variance

18. Select the Master Query.

    Only the Master query will have access to the fields from the sub-queries.

    

19. Create the following calculation in the Master Query, named Variance.

    This calculation will work out the difference between the two years. As sometimes there may be NULL values, this calculation will have to handle these.

    CASE
    WHEN 2016 Sales IS NULL THEN 2017 Sales
    WHEN 2017 Sales IS NULL THEN 0
    ELSE 2017 Sales – 2016 Sales
    END

    

20. Add the Variance field as a Column.

    

21. Format the variance column, adding a $ prefix and 0 decimal places.

    This is the filter that will be used to ensure the 2016 Sales column only reflects the data for 2016.

    

22. Publish your report.