0

Say our Accounts can have Babies. I would like to make a report that shows how often our Accounts have Babies, on average.

So Account A has created a Baby in January and another Baby in June, thus on average A creates a Baby every 6 months.

(And then I want the average of all Accounts.)

Any idea how I could do this? The first step would probably be to look at the Babies' CreatedDate but I have no idea how to go from there..

Thanks for your help!

1 Answer 1

0

You will need to create a report grouped by parent and add a summary field.

Assume today is 20 May 2021

Assuming that there is an account A which has 3 babies created on

  1. 15 May - 5 days before today
  2. 15 April - 35 days before today
  3. 15 March - 65 day before today

So on average a child is created every 30 days.

Implementation:

  1. Create a formula of type number field on Baby object which calculates date from today. (Days__c)
  2. Create a report and group it via parent.
  3. Add a grouping formula at Level 1 grouping.

(Baby__c.Days__c:SUM - (Baby__c.Days__c:MIN * RowCount)) / RowCount

Calculations based on above formula.

Baby__c.Days__c:SUM = 105 Baby__c.Days__c:MIN = 5 RowCount = 3

105 - 15 /3 = 30

This should give you a crude estimate.


Update: I revisited this and noticed that since you want to just calculate the average, you only require the highest and lowest value because the highest number is already sum of all values.

So the new formula would be.

(Baby__c.Days__c:MAX - Baby__c.Days__c:MIN) / (RowCount - 1)

So comparing 2 use cases, we have:

Use case 1:

  1. Baby__c.Days__c:MAX = 65
  2. Baby__c.Days__c:MIN = 5

Result = 30 (60/2)

Use case 2:

  1. Baby__c.Days__c:MAX = 900
  2. Baby__c.Days__c:MIN = 198

Result = 175* (702/4)

The division by total RowCount is not right, instead it should be RowCount - 1 because we are looking to find intervals, which would always be 1 less than total rows

This would not work for 1 or 0 rows so that needs to be handled.

3
  • Thanks for your message! So if we look at this case (i.imgur.com/jVWnRi4.jpeg), the result this gives me is 301 days between each. This doesnt sound quite right since the number of days between each Start Date was respectively 196+331+49+125 (= 701 days, divided by 5 Withdrawals = 140 days between each Withdrawal, not 301). It looks like the issue is that this also takes into account the 198 days between the last Withdrawal and the present day, which it shouldnt. If we remove that, then the formula gives 2301 - 1615 / 5 =137 days. Would you know how to ignore the last one?
    – user256515
    Commented May 20, 2021 at 13:50
  • @user256515 I posted an update
    – manjit5190
    Commented May 20, 2021 at 14:24
  • Yay this works perfectly, thank you very much! Also helped me familiarize myself with summary fields.
    – user256515
    Commented May 20, 2021 at 14:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .