Is it possible to use processes like CalculatedColumn or DifferenceColumn on Pivots? If so, how to reference pivot columns?
For example, I have rows based on Site, subdivided into Annual / Monthly members. I then have columns based on membership status, sub grouped into gender.
I want to add a CalculatedColumn to tell me, for example, the percentage of Active Females of Total Active members. How do I reference aggregated columns? For reference, I've added the Pivot below too.
public function setup()
{
$this->src('query_source')
->query("Select * "
. "From v_active_subscription vas "
. " Inner Join ff_members m on vas.member_id = m.id "
. " Inner Join ff_genders g on g.id = m.gender_id "
. " Where subscription_type not like 'Flex%' "
. " and subscription_status not like 'Suspended%' "
. "Group By concat(lpad(vas.gym_id,2,'0'), ' - ', gym_name), subscription_status, subscription_type, g.gender_name "
. "Order By concat(lpad(vas.gym_id,2,'0'), ' - ', gym_name), subscription_status, subscription_type, g.gender_name;")
->pipe(new Pivot(array(
'dimensions'=>array(
'column'=>'Status,Gender',
'row'=>'Gym,Product'
),
'aggregates'=>array(
'sum'=>'Members'
)
)))
->pipe($this->dataStore('active_members_query_result'));
}