KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Calculated field referencing another calculated field? #42

Open Larry Groebe opened this topic on on Jul 28, 2017 - 6 comments

Larry Groebe commented on Jul 28, 2017

I'm loving the idea of KoolReport -- I can see it simplifying output of a lot of statistical fitness data I am collecting for a client. But I have a situation where KoolReport crashes as I try to compile the dataset. I want to fetch the raw data from a set of fitness statistics, and I need to calculate both the number of "healthy" students and the percentage of students that are healthy. I though I could do that by calculating the number of healthy students first using the group function, and then dividing that by the total number of students. One of the two fields is in my original SQL query but the other was calculated by KoolReport. When I try this second-step calculation, it doesn't recognize my first calculated field . (it DOES find the other field needed which was in the SQL query to begin with.)

Here's an edited version of my sequence:

protected function setup() {
    $this->src('fitttr_data')
    ->query("SELECT 1 as cnt, value, normallo,normalhi,
    DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(dob,'%Y') - (DATE_FORMAT(NOW(),'00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age,
	FROM....
	")

  //for each student, calculate if they were healthy
    ->pipe(new CalculatedColumn(array(
   "ishealthy"=>"{value}>={normallo} && {value}<={normalhi}",
)))

//now group by age, summing up values 
    ->pipe(new Group(array(
        "by"=>"age",
        "avg"=>"value",
        "sum"=>"cnt, ishealthy"
    )))

    //sort by age
    ->pipe(new Sort(array(
        "age"=>"asc"
    )))

    //try to calculate the percentage healthy for each group
	->pipe(new CalculatedColumn(array(
		"pcthealthy"=>"{ishealthy}/{cnt}*100"
	)))

    //and output
    ->pipe($this->dataStore('fitttr_data'));
} 

It's this line -- "pcthealthy"=>"{ishealthy}/{cnt}*100" -- that crashes, failing to find a variable named "ishealthy" Yes, I could put "ishealthy" into the original SQL query, but logically it is clearer to do calculations after getting the raw data. Is this a fixable bug?

--Larry

KoolReport commented on Jul 29, 2017

That's interesting case. Please allow me a little time to replicate the case and come back to you.

KoolReport commented on Jul 29, 2017

I will come back

KoolReport commented on Jul 29, 2017

It is because that we can not sum the false and true value. So you should do this:

->pipe(new CalculatedColumn(array(
   "ishealthy"=>"({value}>={normallo} && {value}<={normalhi})?1:0",
)))
Larry Groebe commented on Jul 30, 2017

Interesting. OK, thank you.

KoolReport commented on Jul 31, 2017

Does it work?

Larry Groebe commented on Jul 31, 2017

Yes, it does. Thank you - most useful to have this option available.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
solved

None