CODEWITHSUNDEEP
phpmysql
user3605720
user3605720

Reputation: 15

Calculate Growth Rate Using PHP and Mysql

I'm trying to calculate the compound annual growth rate (CAGR) of a few products for various time periods. I have a MySQL database that looks like this:

product, asof, sales
abc, 2013-06-30, 36000000
abc, 2013-12-31, 48000000
abc, 2014-01-31, 51000000
abc, 2014-02-28, 56000000
xyz, 2013-06-30, 26000000
xyz, 2013-12-31, 33000000
xyz, 2014-01-31, 33000000
xyz, 2014-02-28, 36000000

I've figured out the function of CAGR should look like this:

// EV = Ending Value
// BV = Beginning Value

function cagr($EV, $BV, $Period ) {
    $cagr = pow(($EV/$BV),(1/$Period)) - 1;
    return $cagr;
}

Any help or direction would be much appreciated! Thanks!

EDIT :

My end goal is to create a table that contains a 3 Month, 1 Year, and 3 Year compound annual growth rate for each product. So more specifically I need help on what MySQL queries are needed and how to structure the PHP in order to populate the table.

EDIT 2 :

So I've got it figured out how to get the CAGR's based on @Barmar help. What I can't figure out is how to use PHP to get this into a table format.

Is the best way to get month end dates for various time periods using strtotime like below?

$lastdate = "2014-04-30";
$month3 = date('Y-m-d', strtotime('last day 3 months ago', strtotime($lastdate)));
$month6 = date('Y-m-d', strtotime('last day 6 months ago', strtotime($lastdate)));
$year1 = date('Y-m-d', strtotime('last day of months 1 year ago', strtotime($lastdate)));
$year3 = date('Y-m-d', strtotime('last day 36 months ago', strtotime($lastdate)));
$year5 = date('Y-m-d', strtotime('last day 60 months ago', strtotime($lastdate)));

I've been using a function to pass in the dates into the MySQL query that @Barmar described below.

function queryproduct ($db, $end_date, $begin_date) {

    $q_product = "SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
                FROM (
                    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
                    FROM test
                                WHERE asof BETWEEN '$begin_date' AND '$end_date'
                    GROUP BY product
                    -- Prevent divide by 0 if we don't have a range
                    HAVING end_date != begin_date) AS r
                JOIN test AS s1 ON s1.product = r.product AND s1.asof = end_date
                JOIN test AS s2 ON s2.product = r.product AND s2.asof = begin_date;";

    $q_result = mysqli_query($db, $q_product);

    return $q_result;
}

Upvotes: 1

Views: 2737

Answers (1)

Barmar
Barmar

Reputation: 781210

SELECT r.product, POWER(s1.sales/s2.sales, 1/(datediff(end_date, begin_date)/365)) - 1 AS cagr
FROM (
    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin_date
    FROM sales
    WHERE asof BETWEEN :range_start AND :range_end
    GROUP BY product
    -- Prevent divide by 0 if we don't have a range
    HAVING end_date != begin_date) AS r
JOIN sales AS s1 ON s1.product = r.product AND s1.asof = end_date
JOIN sales AS s2 ON s2.product = r.product AND s2.asof = begin_date

DEMO

UPDATE:

You can get multiple periods with:

SELECT DISTINCT r1y.product product, end_date,
       begin1y, POWER(s_end.sales/s_1y.sales, 1/(datediff(r1y.end_date, begin1y)/365)) - 1 AS cagr_1y,
       begin3y, POWER(s_end.sales/s_3y.sales, 1/(datediff(r1y.end_date, begin3y)/365)) - 1 AS cagr_3y,
       begin5y, POWER(s_end.sales/s_5y.sales, 1/(datediff(r1y.end_date, begin5y)/365)) - 1 AS cagr_5y
FROM (
    SELECT product, MAX(asof) AS end_date, MIN(asof) AS begin1y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 1 YEAR)
    GROUP BY product
    -- Prevent divide by 0 if we don't have a range
    HAVING end_date != begin1y) AS r1y
JOIN (
    SELECT product, MIN(asof) AS begin3y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 3 YEAR)
    GROUP BY product) AS r3y
ON r1y.product = r3y.product
JOIN (
    SELECT product, MIN(asof) AS begin5y
    FROM sales
    WHERE asof > DATE_SUB(NOW(), INTERVAL 5 YEAR)
    GROUP BY product) AS r5y
ON r1y.product = r5y.product
JOIN sales AS s_end ON s_end.product = r1y.product AND s_end.asof = r1y.end_date
JOIN sales AS s_1y ON s_1y.product = r1y.product AND s_1y.asof = begin1y
JOIN sales AS s_3y ON s_3y.product = r1y.product AND s_3y.asof = begin3y
JOIN sales AS s_5y ON s_5y.product = r1y.product AND s_5y.asof = begin5y

DEMO

Upvotes: 1

Related Questions