Calculation with Group process?

zenon commented on Aug 21, 2017

Hi,

I used Group process to develop Table 2 (data store B) from Table 1 (data store A). But, I want to have the third column, like in Table 3, where the percentage is calculated from the result of Table 2, and divided by the Total (footer).

How can I call out the result of Table 2 to perform CalculatedColumn to show the percentage column?

Appreciate your help.

Thanks.

KoolReport commented on Aug 21, 2017

Have I ever sent you the process called AggregatedColumn? This can be done with this process.

zenon commented on Aug 21, 2017

Yes, I have it. And I managed to just like Table 2. However, I could not figure out how to do the percentage column.

Could you please guide me with this?

Thanks a lot.

KoolReport commented on Aug 21, 2017

After grouping you do:

->pipe(new AggregatedColumn(array(
    "total"=>array("sum","second_column")
)))
->pipe(new CalculatedColumn(array(
    "percent"=>"{second_column}*100/{total}"
)))

zenon commented on Aug 21, 2017

I have done this:

->pipe(new AggregatedColumn(array(
            "a"=>array("sum", "qty"),
        )))
->pipe($this->dataStore("reportA"));

in Table A and

->pipe(new Group(array(
            "by"=>"item",
            "sum"=>"qty",
        )))
->pipe($this->dataStore("reportB"));

in Table B.

Is it possible to take the result from Group and then divided by the result from AggregatedColumn and show this column in Table B?

Appreciate your help.

Thanks a lot.

KoolReport commented on Aug 21, 2017

It is better that you do on 1 table only. You group data first with Group then use AggregatedColumn to add column "total" of quantity, then use the quantity divided by the total quantity.

zenon commented on Aug 21, 2017

Is there any other alternative for this? This is because, I want to remain the raw data, as shown in Table 1(comment 1) as well. I tried to do Group in the same table, however, the original will be overwritten.

KoolReport commented on Aug 21, 2017

If you want to keep the raw data then you can save the data state before data going to Group.

$this->src("mysource")
->pipe(..)
...
->saveTo($raw) //Store this step as $raw
->pipe(new Group(...))
->pipe(new AggragatedColumn(...))
-pipe($this->dataStore("processed_data"));

$raw->pipe($this->dataStore("raw_data"));

zenon commented on Aug 21, 2017

Then how do I use the result from Group to do calculation? This is because both Group and raw data column name is the same, i.e. 'qty'.

KoolReport commented on Aug 22, 2017

The raw and the group route are different so virtually you may not do calculation. I suggest you do all calculation in the group route. If you want to manually manipulate data between two routes, you need to do it at the view page. You can loop through data of two dataStore and do calculation.

zenon commented on Aug 22, 2017

Could you please show me an example, for manipulating data between two routes at the view page?

Appreciate your guidance.

Thanks.

KoolReport's Forum

Calculation with Group process? #84

Build Your Excellent Data Report