Hello all, 

I am stragling with following problem in my Einstein Dashboard. 

I need to use following formula in compare table to calculate the avg. Acctivities per Rep per week for a specific time period (this time period is selected via global filter and can be changed) Formula => A / (B*5)

The compliation is, that as I need to show the averge acitivites per week so I set up the grouping by week. 

Then the Unique Owner Id (B) in my table is causing the problem:

Week1/100 Unique Activities/50 Uniqe Owner Id

Week2/120 Unique Activities/55 Uniqe Owner Id

Week3/90 Unique Activities/60 Uniqe Owner Id

For B I would need the number of unique owner Ids for all

Hello all, 

I am straggling with following problem in my Einstein Dashboard. 

I need to use following formula in compare table to calculate the avg. Activities per Rep per week for a specific time period (this time period is selected via global filter and can be changed) Formula => A / (B*5)

The issue is, that as I need to show the average activities per week, I have to set up the grouping by week. 

Then the Unique Owner Id (B) in my table is causing the problem:

Week1/100 Unique Activities/50 Unique Owner Id

Week2/120 Unique Activities/55 Unique Owner Id

Week3/90 Unique Activities/60 Unique Owner Id

For B I would need the number of unique owner Ids for all three weeks. Which would be let say 65.

The question is, can I solve this with compare tables (1) or bindings (2)? 

(1)

For compare tables I would need to get the table to look like this:

Week1/100 Unique Activities/65 Unique Owner Id

Week2/120 Unique Activities/65 Unique Owner Id

Week3/90 Unique Activities/65 Unique Owner Id

Unfortunately I am not able to arrange the data correctly as I don’t know how to populate the B column with Total Unique Owner Id.

I found some formulas which return the sum of Owner Id  per week = > 165. 

sum(B) over ([..] partition by all)

Can this formula be somehow changed to return Unique Owner Id for sum(B) over ([..] partition by all).

(2)

So I tried to use bindings and created a query to calculate the Unique Owner Ids and then change the JSON. My binding is working as I was able to test it with a text field. So I tried to update the JSON of the widget.

Original code where I included the 65 unique owner id manually (just to get some results):

"formula": "A/(65*5)"

My code with biding:

"formula": "A/({{cell(OwnerId_1.result,0,\"A\").asObject()}}*5)"

This leads to an error: The Formula A/( is invalid.

I would be very thankful if anyone has an idea what I am doing wrong here.

Martin 

three weeks. Which would be let say 65.

The question is, can I solve this with compare tables (1) or bidings (2)? 

(1)

For compare tables I would need to get the table looks like this:

Week1/100 Unique Activities/65 Uniqe Owner Id

Week2/120 Unique Activities/65 Uniqe Owner Id

Week3/90 Unique Activities/65 Uniqe Owner Id

Unfortunately I am not able to arrange the data correctly as I dont know how to populate the B column with Total Unique Owner Id.

I found some formulas which return the sum of Owner Id  per week = > 165. 

sum(B) over ([..] partition by all)

Can this formula be somehow changed to return Unique Owner Id for sum(B) over ([..] partition by all).

(2)

So I tried to use bidings and created a query to calculate the Uniqe Owner Ids and then included in JSON. Biding is working as I was able to test it with a text field. So I tried to update the JSON of the widget.

Original code where I included the 65 unique owner id manually:

"formula": "A/(65*5)"

My code with biding:

"formula": "A/({{cell(OwnerId_1.result,0,\"A\").asObject()}}*5)"

This leads to an error: The Formula A/( is invalid.

I would be very thankfull if anyone has an idea what I am doing wrong here.

Martin