How to generate a multi-year sales comparisons report for your customers?

Overview

This article describes how to generate a "BEST Customers" internal report for a number of years in a side by side comparison format.

Information

For the time being, this report is not readily available in Everest. However, it can be generated by the following steps:

  1. Run the following query either by using the Advanced Query Builder or by using SQL Server Management Studio:

    SELECT 
      i.cust_code,  c.name,
      year(order_date) AS yr,
      sum(inv_amount) AS amt
    FROM
      invoices i WITH (nolock)
      INNER JOIN cust c WITH (NOLOCK) ON (i.cust_Code = c.cust_code)
    WHERE
      status = 9
      and year(order_Date) >= {start_year}
      and year(order_Date) <= {end_year}
    GROUP BY
      i.cust_code,  c.name,
      year(order_date)

    This query will allow you to retrieve the needed data. Replace the following tokens with the appropriate values for your use case:

    1. {start_year} - the start year for the report
    2. {end_year} - the end year for the report
  2. Copy this data and paste it into an Excel Spreadsheet
  3. Use Excel to create a PivotTable, to analyze the data.

 

Comments

0 comments

Please sign in to leave a comment.