Calculate the average with calculated column

ankit commented on Jun 6, 2020

Hi Please help me I am using calculated column with pivot 2D plus and minus working in calculate column process but when I use division or multiplication of two column is not work and when i enter custom value for division like 2 its working please check .

$node = $this->src("mysql")->query($Query)

	->pipe(new CalculatedColumn(array(
        "JSWithRating"=>"{oneStar}+{twoStar}+{threeStar}+{fourStar}+{fiveStar}"
    )))
	->pipe(new CalculatedColumn(array(
		"Average"=>function($row){
            return $row["quantity_c"]/$row["JSWithRatings"];
        }
	)))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, engname",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c, JSWithRating, noRating,Average, oneStar, twoStar, threeStar, fourStar, fiveStar",
        ),
    )))
	
    ->pipe($this->dataStore('tat1Field'));

"measures"=>array(

	"quantity_c - sum",
	"JSWithRating - sum",
	"noRating - sum",
	"Average - sum",
	"oneStar - sum",
	"twoStar - sum",
	"threeStar - sum",
	"fourStar - sum",
	"fiveStar - sum",
	
),

please tell me where I wrong ?

David Winterburn commented on Jun 8, 2020

What do you mean by "not working" and "working"? Was there an error message or were the result not what you want?

A note for you when using dividing, you should first aggregate results then divide them instead of dividing each row before aggregating them.

ankit commented on Jun 8, 2020

I want calculate of division of two column in a new column but its not working like totaj js / with rating = avrage rating

David Winterburn commented on Jun 8, 2020

Did you read this:

A note for you when using dividing, you should first aggregate results then divide them instead of dividing each row before aggregating them.

In your code you divide average for each row before summing them. It's not mathematically correct to get average like that. Sum first (via pivot2D) and then divide average after.

ankit commented on Jun 8, 2020

can give example ?

David Winterburn commented on Jun 8, 2020

->pipe(new Pivot2D(...))
->pipe(new CalculatedColumn(...))

ankit commented on Jun 8, 2020

ErrorException Undefined index: quantity_c http://15.206.180.82/livservReporting_ankit/index.php/getReport

When I do like this its give me above error :

$node = $this->src("mysql")->query($Query)

	->pipe(new CalculatedColumn(array(
        "JSWithRating"=>"{oneStar}+{twoStar}+{threeStar}+{fourStar}+{fiveStar}"
    )))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, engname",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c, JSWithRating, noRating,Average, oneStar, twoStar, threeStar, fourStar, fiveStar",
        ),
    )))
	->pipe(new CalculatedColumn(array(
		"Average"=>function($row){
                return $row["quantity_c"]/$row["JSWithRating"];
        }
	)))
    ->pipe($this->dataStore('tat1Field'));

David Winterburn commented on Jun 8, 2020

After Pivot2D process, there're no longer columns like "quantity_c" or "JSWithRating". Output the result to a Table or DataTables widget to see its columns to apply your calculated columns:

//MyReport.php
->pipe(new Pivot2D(array(...)))
->pipe($this->dataStore('tat1Field'));

//MyReport.view.php
Table::create(array(
    "dataSource" => $this->dataStore('tat1Field'),
    ...

Anurag Choubey commented on Jun 8, 2020

give some example

ankit commented on Jun 8, 2020

not understand your point please tell me how can i archive my target how can I calculate average as per attached image ?

David Winterburn commented on Jun 8, 2020

Which part of my answer did you not understand? I'm telling you to output the result of a Pivot2D process to a datastore and display it in a Table widget to see its columns' structure. After that you could apply CalculatedColumn or any process on those columns.

And see that I'm guiding you the way to solve your very specific problem, not writing your code for you.

ankit commented on Jun 8, 2020

Dear David

I just want a example not full code I mean just syntax overview
I was checked documentation not any example of pivot 2D please provide me if any link

David Winterburn commented on Jun 8, 2020

Hi Ankit,

We are updating the online documentation of Pivot to include Pivot2D process. Will send you a link soon when it's available.

ankit commented on Jun 8, 2020

Thanks for your support David problem solved please share when you update documentation thanks once again for touch

David Winterburn commented on Jun 9, 2020

We've just published documentation for the Pivot2D process at this link:

https://www.koolreport.com/docs/pivot/pivot2D_process/

Please read it and apply it to your case if necessary. Let us know if you have any question. Thanks!

Anurag Choubey commented on Jun 10, 2020

Thanks Can you tell me about that how to use join process in pivot 2D table ? I mean I want to merge of two sql statement result in pivot 2D

ankit commented on Jun 10, 2020

Thanks Can you tell me about that how to use join process in pivot 2D table ? I mean I want to merge of two sql statement result in pivot 2D

David Winterburn commented on Jun 11, 2020

What do you mean by merging 2 sql statement in pivot2D? Do you want to combine/union data before piping to a pivot2D process or do you want to pipe 2 different data to 2 pivot2D processes before merging them? The latter case won't work because pivot2D results can't be merge as they could have totally different structure of row, column and data fields.

ankit commented on Jun 11, 2020

Hi David ,

I have two data set in mysql just like attached images one is of current month and one is of January month now i want this data sets in one single data.

It is possible that we can append 2nd data rows of 1st data rows or vice versa before pivot2D process so that Its will convert in single data sets rows

David Winterburn commented on Jun 11, 2020

There're many ways to combine these data:

1 . Change your sql query where clause:

where (month(a.date_entered) = '02 or month(a.date_entered) = '06') and year(a.date_entered) = '2020'

2 . Use union in your sql queries if they have the same columns;

select ... from ... where ...
union
select ... from ... where ...

3 . Use KoolReport's process like this:

$this->src($src1)
->query($query1)
->saveTo($node);

$this->src($src2)
->query($query2)
->saveTo($node);

$node->pipe(new Pivot2D(...))
...

KoolReport's Forum

Calculate the average with calculated column #1478

Build Your Excellent Data Report