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

    Calculated field for Average scripts per target

    Anuradha Chandra

      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

       

          

      ControlSegmentNameProduct DescPurchase DateSync IDNum T Rx
      Control GroupTRAVATAN Z 5ML1/1/2014 0:004825182.472
      Control GroupTRAVATAN Z 5ML1/1/2014 0:005546752.34
      Control GroupTRAVATAN Z 5ML1/1/2014 0:0014629222.064
      Control GroupTRAVATAN Z 5ML1/1/2014 0:00851098.956
      Control GroupTRAVATAN Z 5ML1/1/2014 0:0063303329.356
      Control GroupTRAVATAN Z 5ML1/1/2014 0:0014024362.292
      Control GroupTRAVATAN Z 5ML1/1/2014 0:006878052.32
      Test GroupTRAVATAN Z 2.5ML1/1/2014 0:00147874.008
      Test GroupTRAVATAN Z 2.5ML1/1/2014 0:00204986
      Test GroupTRAVATAN Z 2.5ML1/1/2014 0:00228173.032
      Test GroupTRAVATAN Z 2.5ML1/1/2014 0:00419926.032
      Test GroupTRAVATAN Z 2.5ML1/1/2014 0:0012911189.008

      Thanks,

      Anu

        • 1. Re: Calculated field for Average scripts per target
          lei.chen.0

          Hello Anuradha,

           

          Could you explain more about this?

          (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
            Sreenadha Reddy Palakolanu

            Hi AnuRadha,

              Can You Explain in Detail?

             

            Regards,

            Sreenadha Reddy Palakolanu

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

              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:

               

              ControlSegmentNameNumber of RecordsNumTargets
              Control Group12,230
              Test Group120,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
                lei.chen.0

                Hello Anuradha,

                 

                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.

                 

                If possible, please click the "Advanced editor and add an attachment", and then you can attach your sample data or workbook.

                 

                 

                Regards.