Pivot's Aggregates Compurations

The example shows how to compute custom aggregates

The report shows how to compute custom aggregates such as average sales per product line/category.

    ->pipe(new Pivot(array(
        "dimensions"=>array(
            "column"=>"orderYear, orderMonth",
            "row"=>"customerName, productLine"
        ),
        "aggregates"=>array(
            "sum"=>"dollar_sales",
            "count distinct" => "productLine",
        ),
        "computations" => array(
            "avgSalesPerDistinctLine" => "{dollar_sales - sum} / {productLine - count distinct}",
            "avgSalesPerDistinctLine" => function($aggRow) {
                return $aggRow["productLine - count distinct"] != 0 ?
                    $aggRow["dollar_sales - sum"] / $aggRow["productLine - count distinct"] : null;
            }
        ),
    )))
<?php
require_once "YearsMonthsCustomersCategories.php";
$YearsMonthsCustomersCategories = new YearsMonthsCustomersCategories;
$YearsMonthsCustomersCategories->run()->render();
?>    
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;

class YearsMonthsCustomersCategories extends koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }
    function setup()
    {
        $node = $this->src('dollarsales');
        
        $node->pipe(new Filter(array(
            array('customerName', '<', 'Am'),
            array('orderYear', '>', 2003)
        )))
        ->pipe(new ColumnMeta(array(
            "dollar_sales"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>"orderYear, orderMonth",
                "row"=>"customerName, productLine"
            ),
            "aggregates"=>array(
                "sum"=>"dollar_sales",
                "count distinct" => "productLine",
            ),
            "computations" => array(
                "avgSalesPerDistinctLine" => "{dollar_sales - sum} / {productLine - count distinct}",
                "avgSalesPerDistinctLine" => function($aggRow) {
                    return $aggRow["productLine - count distinct"] != 0 ?
                        $aggRow["dollar_sales - sum"] / $aggRow["productLine - count distinct"] : null;
                }
            ),
        )))
        ->pipe($this->dataStore('sales'));  
    }
}
<?php

use \koolreport\pivot\widgets\PivotTable;
?>
<div class="report-content" style="overflow: scroll">
    <div class="text-center">
        <h1>Pivot's Aggregates Compurations</h1>
        <p class="lead">
            The example shows how to compute custom aggregates
        </p>
    </div>

    <?php
    $dataStore = $this->dataStore('sales');
    PivotTable::create(array(
        "dataStore" => $dataStore,
        "rowDimension" => "row",
        "columnDimension" => "column",
        "measures" => array(
            "dollar_sales - sum",
            // 'dollar_sales - count',
            "productLine - count distinct",
            "avgSalesPerDistinctLine" => [
                "decimals" => 0,
                "prefix" => "$",
            ]
        ),
        'rowSort' => array(
            'dollar_sales - count' => 'desc',
        ),
        'columnSort' => array(
            'orderMonth' => function ($a, $b) {
                return (int)$a < (int)$b;
            },
        ),
        "showDataHeaders" => true,
        // 'rowCollapseLevels' => array(0),
        // 'columnCollapseLevels' => array(0),
        'width' => '100%',
        'headerMap' => array(
            'dollar_sales - sum' => 'Sales',
            'productLine - count distinct' => 'Distinct product lines',
            'avgSalesPerDistinctLine' => 'Sales per distinct line',
            '1' => 'January',
            '2' => 'February',
            '3' => 'March',
            '4' => 'April',
            '5' => 'May',
            '6' => 'June',
            '7' => 'July',
            '8' => 'August',
            '9' => 'September',
            '10' => 'October',
            '11' => 'November',
            '12' => 'December',
        ),
    ));
    ?>

</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 Get KoolReport Pro