8 Replies Latest reply on Aug 27, 2017 8:47 PM by james chambers

    LOD with calculated field and/or multiple data sources

    james chambers

      I have instagram data across several google-doc worksheets; one tracks individual posts and their associated likes and comments, while one sheet tracks followers.

      I am trying to calculate monthly engagement rate as the following function: 

      step 1: (likes+ comments)/followers= daily engagement

      step 2: avg(daily engagements) grouped by month

       

      this is not the same as summing the monthly (likes+comments) / monthly followers.  Rather I first need to calculate daily engagement then divide by days

       

      heres what i have so far (at the daily level)

      Insta1.JPG

       

      Im trying to achieve something like this  (note the engagement count is only valid when i have values for TotalEngagement and Followers, hence 4 is the value in the chart below rather than 31)

      insta2.JPG

       

      I need a formula that would effectively do something like:

      { fixed [Published] : SUM([engagementRate]) }

      OR

      { fixed [Published] : SUM([Total Engagements]) } / { fixed ([AllFollowers (Instagram)].[Date]) : SUM([AllFollowers (Instagram)].[Followers]) }

       

      The logic in the first attempt is to simply sum the engagement rate while fixing at the date (day level).  I need to use the LOD because ultimately ill remove the specific date from rows and replace with month.  I get the infamous error "Argument to the sum is already an aggregation..."

      So instead i tried doing the math to calculate engagement rate within the formula but i am not allowed to bring in another data source when using an LOD.

       

      Any thoughts?