4 Replies Latest reply on May 4, 2016 4:48 PM by John Sobczak

    How to use Fixed Calculations?

    nick.cook.0

      I am having trouble getting the Fixed calculation to work. Could someone explain how it works?

       

      For example from the dataset below, I want to return Prospects = 100 for Campaign_ID = 123.

       

      100 is the actual prospect size for campaign 123, with actuals at each market level but duplicated for response type OTM and DM. My dataset contains YTD and MTD numbers, which happen to be equal, thus duplicated again. How does fixed work within my dataset?

       

      Campaign IDMarketTimingResponse TypeProspects
      123AYTDOTM10
      123BYTDOTM20
      123CYTDOTM30
      123DYTDOTM40
      123AYTDDM10
      123BYTDDM20
      123CYTDDM30
      123DYTDDM40
      123AMTDOTM10
      123BMTDOTM20
      123CMTDOTM30
      123DMTDOTM40
      123AMTDDM10
      123BMTDDM20
      123CMTDDM30
      123DMTDDM40
        • 1. Re: How to use Fixed Calculations?
          Derrick Austin

          Hey Nick,

           

          It sounds like you should fix your calc to Campaign, Timing, and (I assume) time period.

          {FIXED [Campaign], [Timing], [Time Period] : SUM([Prospects)}

           

          Hope this helps!

          - Derrick

          • 2. Re: How to use Fixed Calculations?
            pooja.gandhi

            Nick!

             

            That would be a calc like this:

             

            { fixed [Campaign ID]: sum([Prospects]) } / { fixed [Campaign ID] : countd([Market]) }

             

            The numerator gives 400 and the denominator gives a 4!

            1 of 1 people found this helpful
            • 3. Re: How to use Fixed Calculations?
              Ashish Chaudhari

              Hi Nick,

               

              If the records are duplicated, then you should be worried about your database. This was really easy if you could have done the proper structuring referring to the RDBMS rules. Doing such a YTD and MTD differentiation is really a thing which is not suggested to do. The reason I am saying this is because for every simple calculation you will have to face the problem of duplication and you need to somehow manipulate the data to get the desired result (which isn't standard) .

               

              If possible please change the data structure to avoid getting stuck with all your future calculations. You will always end up getting altering them for your duplicate data.

               

              Thanks and Regards,

              Ashish Chaudhari

              • 4. Re: How to use Fixed Calculations?
                John Sobczak

                Pooja, This works for these values, but if there were possibly more value outcomes for the timing and response type then I don't think it will be right answer.   I think Derrick's calc will survive this.