Sales By Quarter

Top 5 Customers


CustomerTotalQ1Q2Q3Q4
Euro+ Shopping Channel $820,690 $285,275 $249,674 $64,949 $220,792
Mini Gifts Distributors Ltd. $591,827 $239,256 $7,639 $197,065 $147,867
Australian Collectors, Co. $180,585 $44,895 $53,429 $0 $82,261
Muscle Machine Inc $177,914 $0 $58,841 $20,314 $98,758
La Rochelle Gifts $158,573 $49,524 $41,623 $47,924 $19,502
 

Top 5 Products


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

Top 5 Categories


CategoryTotalQ1Q2Q3Q4
Classic Cars $3,853,922 $868,488 $798,230 $690,564 $1,496,640
Vintage Cars $1,776,787 $416,348 $367,442 $312,314 $680,683
Motorcycles $1,121,426 $231,128 $301,789 $192,440 $396,069
Trucks and Buses $1,024,114 $212,869 $217,235 $180,676 $413,334
Planes $954,638 $218,113 $229,117 $125,644 $381,765

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 hierachial demension, 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
    ));
?>

<div class="report-content">
    <div class="text-center">
        <h1>Sales By Quarter</h1>
        <form method="post">
            <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>
    </div>
    <?php echo $report->run()->render(); ?>
</div>
<?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(
        "sales"=>array(
          'filePath' => '../../../databases/customer_product_dollarsales2.csv',
          'class' => "\koolreport\datasources\CSVDataSource",      
          'fieldSeparator' => ';',
        ),
      )
    );
  }
  function setup()
  {
    $salesYear = $this->params["salesYear"];

    $node = $this->src('sales')
    ->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" => "customerName",
      "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",
      ),
      "customerName"=>array(
        "label"=>"Customer",
      ),
    )))->saveTo($node2);
    
    $node2->pipe($this->dataStore('salesQuarterCustomer'));
    
    $node2->pipe(new RemoveColumn(array(
      "{{all}}"
    )))
    ->pipe($this->dataStore('salesQuarterCustomerNoAll'));
    
    $node2->pipe(new OnlyColumn(array(
      'customerName', "{{all}}"
    )))->pipe($this->dataStore('salesQuarterCustomerAll'));
    
    $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'));
    
    $node->pipe(new Cube(array(
      "row" => "productLine",
      "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",
      ),
      "productLine"=>array(
        "label"=>"Category",
      ),
    )))->saveTo($node2);
    
    $node2->pipe($this->dataStore('salesQuarterProductLine'));
    
    $node2->pipe(new RemoveColumn(array(
      "{{all}}"
    )))->pipe($this->dataStore('salesQuarterProductLineNoAll'));
    
    $node2->pipe(new OnlyColumn(array(
      'productLine', "{{all}}"
    )))->pipe($this->dataStore('salesQuarterProductLineAll'));
  }
}
<?php
    use \koolreport\widgets\google;
    use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
  <div class='page'>
    <h3 class="text-danger">Top 5 Customers</h3>
    <hr style="background-color:#ff5454"/>

    <?php 
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterCustomer'),
      ));
    ?>

    <div class='row'>
      <div class="col-sm-6">
        <?php 
          google\BarChart::create(array(
            "dataStore"=>$this->dataStore('salesQuarterCustomerNoAll'),
            "options"=>array(
              'title' => 'Top 5 Customers\' Quarterly Sales',
              'isStacked' => true
            ),
            "width"=>'100%',
            // 'height'=>'400px',
          ));
        ?>
      </div>
      <div class="col-sm-6">
        <?php 
          google\PieChart::create(array(
            "dataStore"=>$this->dataStore('salesQuarterCustomerAll'),
            "options"=>array(
              'title' => 'Top 5 Customers\' Yearly Sales',
              // 'legend' => 'bottom',
              // 'is3D' => true,
            ),
            "width"=>'100%',
            // 'height'=>'300px',
          ));
        ?>
      </div>
    </div>
    <div class='row kreport-horizontal-line-2'> </div>
  </div>
  
  <div class='page'>
    <h3 class="text-danger">Top 5 Products</h3>
    <hr style="background-color:#ff5454"/>

    <?php 
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductName'),
      ));
    ?>
    <div class='row'>
      <div class="col-sm-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-sm-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>
  
  <div class='page'>
    <h3 class="text-danger">Top 5 Categories</h3>
    <hr style="background-color:#ff5454"/>
    
    <?php 
      Table::create(array(
        "dataStore" => $this->dataStore('salesQuarterProductLine'),
      ));
    ?>
    <div class='row'>
      <div class="col-sm-6">
        <?php 
          google\BarChart::create(array(
            "dataStore"=>$this->dataStore('salesQuarterProductLineNoAll'),
            "options"=>array(
              'title' => 'Top 5 Categories\' Quarterly Sales',
              'isStacked' => true
            ),
            "width"=>'100%',
            // 'height'=>'400px',
          ));
        ?>
      </div>
      <div class="col-sm-6">
        <?php 
          google\PieChart::create(array(
            "dataStore"=>$this->dataStore('salesQuarterProductLineAll'),
            "options"=>array(
              'title' => 'Top 5 Categories\' Yearly Sales',
            ),
            "width"=>'100%',
            // 'height'=>'300px',
          ));
        ?>
      </div>
    </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