Customers vs Quarters Cube

Top 5 products with sales by quarters

ProductTotalQ1Q2Q3Q4
1992 Ferrari 360 Spider red $276,840 $58,768 $63,682 $45,788 $108,601
2001 Ferrari Enzo $190,756 $48,887 $39,503 $31,908 $70,458
1952 Alpine Renault 1300 $190,018 $43,938 $34,944 $33,461 $77,675
2003 Harley-Davidson Eagle Drag Bike $170,686 $31,199 $52,913 $25,784 $60,790
1968 Ford Mustang $161,531 $35,777 $27,497 $36,575 $61,682

The report show how to use Cube package to report the sale by customers vs quarters in year. The report is useful to see which customers spend most in a particular quarter of year. This would be a great insight for company to plan inventory for next year. The report also have year selection on top to let us only choose those year that we are interested in.

The data pulled from CSV contains customerName, quarter that they ordered and the saleamount. Cube processed will turn those data into table in which customerName is grouped on row and quarter is group on column.

Cube can be considered a simple version of Pivot Tables that you see in Excel or any Speadsheet application. The different between Cube and Pivot is the number of dimension they handle. While Pivot can handle more than 2 dimension and support hierarchical dimension, Cube support only 2 dimensions and single level of dimension. Although it sounds simple but according to our observation, 70% cases Cube is enough.

Because of the simplicity, the power of Cube process lie on its speed to handle data compared to Pivot.

<?php
    require_once "SalesQuarters.php";
    $salesYear = isset($_POST['salesYear']) ? $_POST['salesYear'] : array(2003, 2004, 2005);

    $report = new SalesQuarters(array(
        'salesYear' => $salesYear
    ));
    echo $report->run()->render(); ?>
<?php
require_once "../../../../koolreport/autoload.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Limit;
use \koolreport\processes\Sort;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\OnlyColumn;
use \koolreport\processes\Filter;
use \koolreport\processes\ValueMap;
use \koolreport\cube\processes\Cube;
use \koolreport\core\Utility;

class SalesQuarters extends koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }
    function setup()
    {
        $salesYear = $this->params["salesYear"];

        $node = $this->src('dollarsales')
        ->pipe(new ColumnMeta(array(
            "dollar_sales"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->pipe(new ValueMap(array(
            'orderQuarter' => array(
                '{func}' => function ($value) {
                    return 'Q' . $value;
                },
                "{meta}" => array(
                    "type" => "string"
                ),
            )
        )));
        
        $filters = array('or');
        foreach ($salesYear as $year)
            array_push($filters, array('orderYear', '=', ''.$year));
        $node = $node->pipe(new Filter($filters));
        
        $node->pipe($this->dataStore('salesFilter'));
        
        $node->pipe(new Cube(array(
            "row" => "productName",
            "column" => "orderQuarter",
            "sum" => "dollar_sales"
        )))
        ->pipe(new Sort(array(
            '{{all}}' => 'desc'
        )))
        ->pipe(new Limit(array(
            5, 0
        )))->pipe(new ColumnMeta(array(
            "{{all}}"=>array(
                "label"=>"Total",
            ),
            "productName"=>array(
                "label"=>"Product",
            ),
        )))->saveTo($node2);
        
        $node2->pipe($this->dataStore('salesQuarterProductName'));
        
        $node2->pipe(new RemoveColumn(array(
            "{{all}}"
        )))
        ->pipe($this->dataStore('salesQuarterProductNameNoAll'));
        
        $node2->pipe(new OnlyColumn(array(
            'productName', "{{all}}"
        )))->pipe($this->dataStore('salesQuarterProductNameAll'));
        
    }
}
<?php
    use \koolreport\widgets\google;
    use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
  
    <div class="text-center">
        <h1>Customers vs Quarters Cube</h1>
        <p class="lead">
            Top 5 products with sales by quarters
        </p>
    </div>
    <?php
        $salesYear = isset($_POST['salesYear']) ? 
            $_POST['salesYear'] : array(2003, 2004, 2005);
    ?>
    <form method="post" class="text-center">
        <div class="form-group">
            <span style="margin-left:10px;">
                <input id="y2003" type="checkbox" name="salesYear[]" value="2003"
                <?php echo in_array(2003, $salesYear) ? 'checked' : '' ?> />
                <label for="y2003">2003</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2004" type="checkbox" name="salesYear[]" value="2004" 
                <?php echo in_array(2004, $salesYear) ? 'checked' : '' ?> />
                <label for="y2004">2004</label>
            </span>
            <span style="margin-left:10px;">
                <input id="y2005" type="checkbox" name="salesYear[]" value="2005" 
                <?php echo in_array(2005, $salesYear) ? 'checked' : '' ?> />
                <label for="y2004">2005</label>
            </span>
        </div>
        <div class="form-group">
            <button type="submit" class="btn btn-primary">Submit</button>
        </div>
    </form>
    <?php 
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductName'),
      ));
    ?>
    <div class='row'>
        <div class="col-md-6">
            <?php 
            google\BarChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameNoAll'),
                "options"=>array(
                'title' => 'Top 5 Products\' Quarterly Sales',
                'isStacked' => true
                ),
                "width"=>'100%',
                // 'height'=>'400px',
            ));
            ?>
        </div>
        <div class="col-md-6">
            <?php 
            google\PieChart::create(array(
                "dataStore"=>$this->dataStore('salesQuarterProductNameAll'),
                "options"=>array(
                'title' => 'Top 5 Products\' Yearly Sales',
                ),
                "width"=>'100%',
                // 'height'=>'300px',
            ));
            ?>
        </div>
    </div>

</div>
customerNameproductNameproductLineorderDateorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 3726.45
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 1768.3300000000002
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5571.8
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5026.14
Baane Mini Imports 1958 Setra Bus Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3284.28
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3307.5
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 1283.48
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2489.13
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2164.4
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2173

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport