Multi-columns SUM - KoolReport

arthur commented on Apr 30, 2018

Hi,

I want to make a table for each salesman with the sum of their sells and growth in the same table, and the targets % accomplished.

For now, I Know how to make the sum and % for one element :

 ->pipe(new Group(array(
            "sum"=>"sells",
             "by"=> "Date",
        )))

->pipe(new CopyColumn(array(
          "indicator"=>"sells",
          'decimals'=>1,
        )))

An in my view :

Table::create(array(
    "dataStore"=>$this->dataStore('marge_delta_rec'),
        "columns"=>array(
            "Date"=>array(
                "label"=>"Date"
            ),
            "sells"=>array(
                "label"=>"Marge Delta Rec"
            ),
              "indicator"=>array(
                "label"=>"Objectif",
                "formatValue"=>function($value){
                    return ($value/ 600) * 100 ."%"; 
                },
                

            )
        ),

));

This seems to work fine but how can I have another column with the sum of the grotwh and a last column with the % accomplished ? If I'm not clear, here is a projection :

KoolReport commented on Apr 30, 2018

If you want to to get sum of another column, you use AggregatedColumn, for example:

->pipe(new AggregatedColumn(array(
    "sellsSum"=>=>array("sum","sells")
)))

More information of AggregatedColumn.

arthur commented on May 1, 2018

Thanks ! So if I understand well, I could do this :

->pipe(new AggregatedColumn(array(
    "sellsSum"=>=>array("sum","sells"),
"AnothersellsSum"=>=>array("sum","Anothersells")
)))

->pipe(new CalculatedColumn(array(
    "objectif1"=>"({sellsSum}/600)*100",
 "objectif2"=>"({AnothersellsSum}/22500)*100",
)))

KoolReport commented on May 1, 2018

I think I may not understand what you want. If you just want to get another columns just like "Marge Delta Rec" and "Objectif" then you just can sum another in group:

->pipe(new Group(array(
    "by"=>"Date",
    "sum"=>array("sells","anotherColumn")
)))
->pipe(new CalculatedColumn(array(
    "objectif1"=>"({sellsSum}/600)*100",
    "objectifAnotherColumn"=>"({anotherColumn}/600)*100",
)))

arthur commented on May 1, 2018

Thanks but I have this error : Parse error: syntax error, unexpected '{' in /home/napsisfrba/www/tdv/koolreport/processes/CalculatedColumn.php(104) : eval()'d code on line 1

KoolReport commented on May 1, 2018

Your data might contain "null" value so better you use function:

   "objectifAnotherColumn"=>function($data)
    {
        return ($data["anotherColumn"]/600)*100;    
    }

arthur commented on May 1, 2018

Perfect thanks ! Last question : in the equation I divide by 600. But what if this value depends on the Session ? I've tried to use if/elseif but that return an error (if not expected)... Thanks a lot

KoolReport commented on May 1, 2018

Somewhere before you initialize the report, you need to set value for session $_SESSION["value"] = 600; and in the function, you use the $_SESSION

   "objectifAnotherColumn"=>function($data)
    {
        return ($data["anotherColumn"]/$_SESSION["value"])*100;    
    }

arthur commented on May 2, 2018

Perfect Thanks ! And would it be possible to aggregate rows ? I explain, here is the table I got

I got my amount by date (here is by month). But what if I want to have the sum per column ? (as a new row)

KoolReport commented on May 2, 2018

Yes, that is exactly what AggregatedColumn does as I show you in the first answer post. The AggregatedColumn will create new column containing sum of all value from another column. For example, you can create the sellsSum column from sells. It will sum value of all rows in column sells and put to sellsSum, then you can use the CalculatedColumn to calculate percentage.

arthur commented on May 2, 2018

Problem is it's making the sum of the whole column of my excel file and the date filter doesnt work on it. It aggregate my file column and not my table column.

KoolReport commented on May 2, 2018

I see, you follow this example to create aggregated footer on Table

<?php 
    Table::create(array(
        ...
        "showFooter"=>"bottom",
        "columns"=>array(
            "amount"=>array(
                "footer"=>"sum"
            ),
            "sale"=>array(
                "footer"=>"avg",
                "footerText"=>"Avg Sale: @value",
            )
        ),
        ...
    ));
?>

arthur commented on May 2, 2018

Amazing! Thanks very much

KoolReport's Forum

Multi-columns SUM #267

Build Your Excellent Data Report