Hi All
In CRM for Outlook there is a section dedicated to Sales stats and KPI numbers. We have had some questions about how these numbers should be read and how there are calculated. This article describes this:
Top 5 Sales:
This shows Top 5 items on invoices in the current year order by the line total in LC that item generated. Following SQL is used
SELECT
TOP 5 T0.ItemCode, ISNULL((SELECT ISNULL(TS0.ItemName,'') FROM OITM TS0 WHERE TS0.ItemCode = T0.ItemCode), '') AS Description, SUM(T0.LineTotal) AS Total
FROM INV1 T0 JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.ItemCode IS NOT NULL AND T1.CardCode = @CardCode AND YEAR(T1.DocDate) = @ThisYear
GROUP BY T0.ItemCode ORDER BY SUM(T0.LineTotal) DESC
It can be argued if it should subtract credit notes but we decided against it to show the interest level (what they want the most) and not the true figures...
KPI Numbers:
Show the lifetime stats of the customer + Sales/Purchase Year to date and Month to date
Following SQL is used for Customers to calculate the individual numbers
DECLARE @CardCode NVARCHAR(6);
SET @CardCode = N'1234';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
(SELECT AVG(T0.DocTotal) FROM OQUT T0 WHERE T0.CardCode = @CardCode) AS averageQuotationSize,
(SELECT AVG(T0.DocTotal) FROM ORDR T0 WHERE T0.CardCode = @CardCode) AS averageOrderSize,
(SELECT AVG(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode) AS averageInvoiceSize,
(SELECT AVG(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode) AS averageCreditNoteSize,
(SELECT COUNT(*) FROM OQUT T0 WHERE T0.CardCode = @CardCode) AS numberOfQuotations,
(SELECT COUNT(*) FROM OQUT T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM QUT1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfCopyToQuotations,
(SELECT COUNT(*) FROM ORDR T0 WHERE T0.CardCode = @CardCode) AS numberOfOrders,
(SELECT COUNT(*) FROM OINV T0 WHERE T0.CardCode = @CardCode) AS numberOfInvoices,
(SELECT COUNT(*) FROM OINV T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM INV1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfInvoicesWithCreditNotes,
(SELECT AVG(T0.CloPrcnt) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS averageSalesOpportunitiesClosePercentage,
(SELECT AVG(T0.MaxSumLoc) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS averageSalesOpportunitiesPotential,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode) AS numberOfSalesOpportunitiesCreated,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.CloseDate IS NOT NULL) AS numberOfSalesOpportunitiesClosed,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.Status = 'W') AS numberOfSalesOpportunitiesWon,
(SELECT COUNT(*) FROM OOPR T0 WHERE T0.CardCode = @CardCode AND T0.Status = 'L') AS numberOfSalesOpportunitiesLost,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)) AS YTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)) AS LASTYTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)) AS MTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OINV T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORIN T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)) AS LASTMTD
Following SQL is used for Vendors to calculate the individual numbers
DECLARE @CardCode NVARCHAR(6);
SET @CardCode = N'1234';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
(SELECT AVG(T0.DocTotal) FROM OPQT T0 WHERE T0.CardCode = @CardCode) AS averageQuotationSize,
(SELECT AVG(T0.DocTotal) FROM OPOR T0 WHERE T0.CardCode = @CardCode) AS averageOrderSize,
(SELECT AVG(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode) AS averageInvoiceSize,
(SELECT AVG(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode) AS averageCreditNoteSize,
(SELECT COUNT(*) FROM OPQT T0 WHERE T0.CardCode = @CardCode) AS numberOfQuotations,
(SELECT COUNT(*) FROM OPQT T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM PQT1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfCopyToQuotations,
(SELECT COUNT(*) FROM OPOR T0 WHERE T0.CardCode = @CardCode) AS numberOfOrders,
(SELECT COUNT(*) FROM OPCH T0 WHERE T0.CardCode = @CardCode) AS numberOfInvoices,
(SELECT COUNT(*) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND (SELECT COUNT(*) FROM PCH1 TS0 WHERE TS0.DocEntry = T0.DocEntry AND TS0.TargetType <> -1) <> 0) AS numberOfInvoicesWithCreditNotes,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170101' AND '20170504'),0)) AS YTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20160101' AND '20160504'),0)) AS LASTYTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170501' AND '20170504'),0)) AS MTD,
(ISNULL((SELECT SUM(T0.DocTotal) FROM OPCH T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)-ISNULL((SELECT SUM(T0.DocTotal) FROM ORPC T0 WHERE T0.CardCode = @CardCode AND T0.DocDate BETWEEN '20170401' AND '20170404'),0)) AS LASTMTD
Comments
0 comments
Please sign in to leave a comment.