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

# LOD with calculated field and/or multiple data sources

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)

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)

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?

• ###### 1. Re: LOD with calculated field and/or multiple data sources

Hi James,

I don't think you can use LOD because you use Data Blending.

Instead, you can use Table calc.

Engage % uses secondary data source.

Set Table calc.

Final view

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

1 of 1 people found this helpful
• ###### 2. Re: LOD with calculated field and/or multiple data sources

this is helpful; i was able to replicate your configuration with my data and get a similar output.  Thank you!

I am having some trouble using the calculated engagement rate in other form factors, like plotting it by month over time or adding a 2nd or 3rd brand to the matrix.

The line chart doesnt cooperate with the index filter.

When i try to add other brands, the values re-calculate to incorrect values.

Is there a way to ensure the output is flexible enough to create various charts?

1 of 1 people found this helpful
• ###### 3. Re: LOD with calculated field and/or multiple data sources

James,

Table calc is flexible enough, but needs to be very carefully set up.

In your case, I hope below helps, but not sure without data.

If you can attach packaged workbook or excel file or whatever with enough volume of data,

I think I can replicate.

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

1 of 1 people found this helpful
• ###### 4. Re: LOD with calculated field and/or multiple data sources

See workbook attached:

some issues:

• when i check another filter value, the values in window changes
• cant figure out how to turn this into a trending line graph with date on x axis and line reflecting engagement rate
• ###### 5. Re: LOD with calculated field and/or multiple data sources

James,

To make it understandable, get rid index filter, and put publish in the table.

To make this calc happen, table calc set as following.

To make table setting easier, I revised formula.

index filter needs to bet exactly same way.

Then convert this to graph.

Move each field into below position and change the type to "Line".

Within same worksheet, you don't lose table calc settings unless you keep all fields in the table.

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

1 of 1 people found this helpful
• ###### 6. Re: LOD with calculated field and/or multiple data sources

AMAZING!!! Exactly what I needed.  thank you, thank you, Shin

• ###### 7. Re: LOD with calculated field and/or multiple data sources

James,

You are very welcome.

Regards,

Shin

<< Ambassador Spotlight Here ! >>