Calculation on multiple fields in pivot table

sunil commented on Apr 14, 2020

Hi, Please suggest me how to calculate percentage based on multiple column. suppose i have two column col1 and col2 i want to sum(col1) and sum(col2) values. percentage = sum(col1)/sum(col2)*100 to display in pivot table.

I am tried with this code but percent comes blank. ->pipe(new AggregatedColumn(array(

        "total_close"=>array("sum","Call_closed")
    )))
    ->pipe(new AggregatedColumn(array(
        "total_cancel"=>array("sum","Call_cancelled")
    )))
    ->pipe(new CalculatedColumn(array(
        "percent"=>"{total_cancel}*100/{total_close}"
    )))

David Winterburn commented on Apr 15, 2020

Hi,

I'm not sure about your question. Can you please show us your Pivot code?

sunil commented on Apr 15, 2020

yes sure.

$Query = "SELECT

    CASE WHEN b.rescheduled_reason_c='Call_Cancelled' THEN '1' ELSE '0' END AS Call_cancelled,
    CASE WHEN b.rescheduled_reason_c!='Call_Cancelled' THEN '1' ELSE '0' END AS Call_closed
    .....................................................";        
    $node = $this->src("mysql")->query($Query)
    ->pipe(new AggregatedColumn(array(
        "total_close"=>array("sum","Call_closed")
    )))
    ->pipe(new AggregatedColumn(array(
        "total_cancel"=>array("sum","Call_cancelled")
    )))
    ->pipe(new CalculatedColumn(array(
        "percent"=>"{total_cancel}*100/{total_close}"
    )))
    ->pipe(new Pivot(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c,address_state,source,CSCName,product_name,cateName,brand",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c,Call_cancelled,Call_closed",
            "count" => "quantity_c",
            //'sum percent' => 'Call_closed'
        )
    )))        
    ->pipe($this->dataStore('SRCancelation'));

PivotMatrix::create(array(

"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('SRCancelation'),
//'hideSubtotalRow' => true,
'showDataHeaders' => true,
"scope" => array(
    "reportType" => $this->params["reportType"],
    "zone" => $this->params["zone"],
    "state" => $this->params["state"],
    "csc" => $this->params["csc"],
    "sourceCall" => $this->params["sourceCall"],
    "brand" => $this->params["brand"],
    "fromDate" => $this->params["fromDate"],
    "toDate" => $this->params["toDate"],
    "year" => $this->params["year"],
    "month" => $this->params["month"],
    "quarter" => $this->params["quarter"],
    
),
"measures"=>array(
    "quantity_c - sum", 
    "Call_cancelled - sum", 
    "Call_closed - sum",
    "percent"  // should be (Call_cancelled - sum *100)/(Call_closed - sum)   but display blank.
),

David Winterburn commented on Apr 15, 2020

Hi,

Please remove the AggregatedColumn and CalculatedColumn to get total_cancel, total_close or percent before the Pivot process. Instead you just need to sum call_cancelled and call_closed in Pivot2D, then divide those after Pivot2D like this:

//MyReport.php
    $node = $this->src("mysql")->query($Query)
    ->pipe(new \koolreport\pivot\processes\Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c,address_state,source,CSCName,product_name,cateName,brand",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c,Call_cancelled,Call_closed",
            "count" => "quantity_c"
        )
    )))        
    ->pipe(new \koolreport\processes\Map(array(
        "{value}" => function($row, $meta) {
            $colList = [];
            $columns = array_keys($meta["columns"]);
            foreach ($columns as $colName)
                if (strpos($colName, " || ") !== false) {
                    $col = substr($colName, 0, strrpos($colName, " || "));
                    if (! isset($colList[$col])) $colList[$col] = true;
                }

            foreach ($colList as $col => $v) {
                $colNameCancel = "$col || Call_cancelled - sum";
                $colNameClose = "$col || Call_closed - sum";
                $colNamePercent = "$col || cancel per close";
                $cancel = Util::get($row, $colNameCancel, 0);
                $close = Util::get($row, $colNameClose, 0);
                $row[$colNamePercent] = $close !== 0 ? $cancel * 100 / $close : 0;
            }
            
            return $row;
        }
    )))
    ->pipe($this->dataStore('SRCancelation')); 

//MyReport.view.php
PivotMatrix::create(array(
    ...
    "measures" => array(
        ...
        "cancel per close"
    )
    ...
));

KoolReport's Forum

Calculation on multiple fields in pivot table #1386

Build Your Excellent Data Report