Hi ,
I got 2 aggregate levels in my data. One is the parent level and then we have the child level.
For example in my case,
Parent : Dormant is the parent
Child: Dormant 35 percent and Dormant 45 percent
Dormant is the main campaign and then we have 2 sub divisions one campaign with 35% and another with 45%.
I got 2 sets of customers . One is the targeted customer group and other control customer group.
Each customer is assigned each group based on the field 'control '. If control = 0, then the customer is a targeted customer otherwise control customer .
I calculate conversions and then conversion rate for mailed and control in 2 diffeerent pivot tables .
I need to do a calculation which gives me
Expected Number of conversions = Number of Mailed customers *Overall Parent Control conversion rate
To be more clear
In the control pivot table, the conversion rate for dormant 35% is 1.77 and 50% is 0.84% .
First I create a calculated field which gives me the overall conversion rate which is 1.29%
In the mailed pivot table , I get exp number of conversions for
Dormant 35% as 460 * 1.29% = 5.95
Howveer When I am addint this metrics, the sales becomes 0 . If I remove expected number of conversions, then the sales is displayed.
Exp Number of conversions = min({Control Conversion Rate Parent level} * {Mailed_count_Child _level})
Control converted is calculated as distinctCountOver({Control Converted}, [parent level}], PRE_AGG)
Sales child level is the sum of revenue.
Can we not do multiplication on fields on different aggregate level ?
Control Conversion is calculated on parent level and then I am multiplying this on the number of mailed customers on child level .
