help with Pivot table

paulo commented on Dec 7, 2022

Hi there, I have used Pivot table in the past,but now I am struggling with calculation: Query result will return few columns: financial_year , Name, numer_of_days, Comm

So,I am trying to Pivot this to have name showing as a row, fiscal_year as a column, and then a calculated column multiplying the number of records under the 'Name" number of days. So count(v)numer_of_days, and then the AVG(comm), and then comm/number of days as PerDay

thank you very much

Sebastian Morales commented on Dec 7, 2022

I think your hard requirement is about calculated avg(comm)/count(number_of_days) or avg(comm)/sum(number_of_days). It's a perfect request for a new Pivot feature, aggregate computation, as demonstrated here:

https://www.koolreport.com/examples/reports/pivot/aggregates_computations/

Here's what your Pivot setup might be:

//MyReport.php
    ->pipe(new Pivot(array(
        "dimensions"=>array(
            "column"=>"financial_year",
            "row"=>"Name"
        ),
        "aggregates"=>array(
            "avg"=>"comm",
            "count" => "number_of_days",
            // "sum" => "number_of_days",
        ),
        "computations" => array(
            // "avgCommPerDays" => "{comm - avg} / {number_of_days - count}", // use expression
            "avgCommPerDays" => function($aggRow) { // or function
                return $aggRow["number_of_days - count"] != 0 ?
                    $aggRow["comm - avg"] / $aggRow["number_of_days - count"] : null;
            }
        ),
    ))) 
    ...

//MyReport.view.php
    PivotTable::create(array(
        "dataStore" => $dataStore,
        "measures" => array(
            "comm - avg",
            "number_of_days - count",
            "avgCommPerDays"
        ), 
    ...

paulo commented on Dec 7, 2022

thanks but my table content came back blank.. here is what I tried to do: Two calculations: tour_weight = number of days * number of tours. Avg commission per day = total comm / number of days (or avg comm)
neither worked for me

            "aggregates"=>array(
               // "sum"=>"CloseCommAdjCurrency",
                "sum" => "numer_of_days",
                "count"=>"numer_of_days",
                "avg" => "CloseCommAdjCurrency"
            ),
            "computations" => array(
                "tour_weight"=>"{numer_of_days - sum} * {numer_of_days - count}",
                // "avgCommPerDays" => "{comm - avg} / {number_of_days - count}", // use expression
                "avgCommPerDays" => function($aggRow) { // or function
                    return $aggRow["numer_of_days - count"] != 0 ?
                        $aggRow["comm - avg"] / $aggRow["numer_of_days - count"] : null;
                }
            ),

"measures" => array(

            "comm - avg",
            "tour_weight"
        ),

thank you

Sebastian Morales commented on Dec 8, 2022

I think there's no avg of comm in your Pivot's aggregates settting:

            "aggregates"=>array(
                 ...
                "avg" => "CloseCommAdjCurrency"
            ),

Therefore in your Pivot's computations, there's no value for "comm - avg". If you meant to use CloseCommAdjCurrency, in computations it should be "CloseCommAdjCurrency - avg".

paulo commented on Dec 8, 2022

thanks. I have updated everything , re-wrote the code with the following, but receiving an error message now "aggregates"=>array(

               // "sum"=>"CloseCommAdjCurrency",
                "sum" => "number_of_days",
                "count"=>"number_of_days",
                "avg" => "CloseCommAdjCurrency"
            ),
            "computations" => array(
                "tour_weight"=>"{number_of_days - sum} * {number_of_days - count}",

VIEW: "measures" => array(

            'number_of_days - sum',
            'number_of_days - count',
            'CloseCommAdjCurrency - avg',
            "tour_weight"
        ),
        "showDataHeaders" => true,
        'headerMap' => array(
            'number_of_days - sum' => 'totalDays',
            'number_of_days - count' => 'NumberofDaysCount',
            'CloseCommAdjCurrency - avg' => 'Avg Comm',
            "tour_weight"=> "tour_weight"
        ),
        'columnSort' => array(
            'financial_year' => function ($a, $b) {
                return (int)$a > (int)$b;
            },
        ),

ERROR: str_replace(): Argument #2 ($replace) must be of type string when argument #1 ($search) is a string

foreach ($computations as $computationName => $formulaOrFunc) { if (is_string($formulaOrFunc)) { $formula = $formulaOrFunc; // echo "row = "; print_r($row); echo "<br>"; foreach ($row as $k => $v) { $formula = str_replace('{' . $k . '}', $v, $formula); } // echo "formula = $formula <br>"; $this->data[$dn][$computationName] = $evaluator->execute($formula); } else if (is_callable($formulaOrFunc)) { $func = $formulaOrFunc;

second line: str_replace('{column}', array('financial_year' => '{{all}}'), '5127 * 959')

3rd line: $this->processAggregateComputations();

thank you

paulo commented on Dec 11, 2022

Hi there, any tip/advise on how I can start debugging this ? thanks

Sebastian Morales commented on Dec 12, 2022

Thanks for your feedback. This looks like a bug with aggregate computations when using expression form. For now pls use the computation function form like this:

            "computations" => array(
                "tour_weight"=>function($aggRow) {
                    return $aggRow["number_of_days - sum"] * $aggRow["number_of_days - count"];
                }
            )

paulo commented on Dec 12, 2022

thank you. It seems to work if using the function. thank you very much Paulo

KoolReport's Forum

Help with Pivot table #2896

Build Your Excellent Data Report