4 Replies Latest reply on Jun 27, 2016 11:09 PM by lei.chen.0

# Calculated field for Average scripts per target

Hi,

I need help writing a calculation for the following: (Avg Scripts per Test Target by month – Avg Scripts per Control group target by month)  - Average monthly test control gap for 12 months prior to campaign

I have a data source that has the data I need for the above data but not sure how to write a calculation. Please help

 ControlSegmentName Product Desc Purchase Date Sync ID Num T Rx Control Group TRAVATAN Z 5ML 1/1/2014 0:00 482518 2.472 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 554675 2.34 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 1462922 2.064 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 85109 8.956 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 633033 29.356 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 1402436 2.292 Control Group TRAVATAN Z 5ML 1/1/2014 0:00 687805 2.32 Test Group TRAVATAN Z 2.5ML 1/1/2014 0:00 14787 4.008 Test Group TRAVATAN Z 2.5ML 1/1/2014 0:00 20498 6 Test Group TRAVATAN Z 2.5ML 1/1/2014 0:00 22817 3.032 Test Group TRAVATAN Z 2.5ML 1/1/2014 0:00 41992 6.032 Test Group TRAVATAN Z 2.5ML 1/1/2014 0:00 1291118 9.008

Thanks,

Anu

• ###### 1. Re: Calculated field for Average scripts per target

(Avg Scripts per Test Target by month – Avg Scripts per Control group target by month)  - Average monthly test control gap

In your sample data, which column stands for "Test Target" and which column stands for "Control group target"?

Regards.

• ###### 2. Re: Calculated field for Average scripts per target

Can You Explain in Detail?

Regards,

• ###### 3. Re: Calculated field for Average scripts per target

Hi Thank you for your reply. Where ever it says Test/Control, it is ControlSegmentName column. And Num T Rx is the script column. I have the formula to calculate Avg Scripts: SUM([Num T Rx])/SUM([NumTargetsByControlSegmentName].[NumTargets]).

[NumTargetsByControlSegmentName].[NumTargets] is coming from an another data source with just 2 values:

 ControlSegmentName Number of Records NumTargets Control Group 1 2,230 Test Group 1 20,270

Data for my formula is there in the data source sample i posted above, I'm not sure how to pull it.

As reference when targets were selected we used Dec 2014 to Nov 2015 Control targets averaged 138.33 Trav Z TRxs and Test Targets averaged 135.85. So the difference between them was 2.5 then the monthly average was 0.2083333

Avg Scripts per Test Target by month = Total Brand Scripts for Test Group / Total number of Targets in Test Group by Month

Avg Scripts per Control Group target by month = Total Brand Scripts for Control Group / Total number of Targets in Control Group by Month

Example: So if Trav Z test targets averaged 12.5 Trav Z TRXs in August and Control Targets averaged 12.6 We would plot +.108333

12.5 – 12.6 - - .208333 = -0.1 + 0.208333 = 0.108333

Any help is much appreciated.

Thanks,

Anu

• ###### 4. Re: Calculated field for Average scripts per target

Thanks!. It sounds like you are trying to forecast by using the difference (in your example, 0.2083333).

Does the "NumTargets" of Test/Control change with time?

>Data for my formula is there in the data source sample i posted above, I'm not sure how to pull it.