5 Replies Latest reply on Jun 13, 2018 5:11 PM by Shinichiro Murakami

# Sum First Instance of a value and use in a calculation

Hello Friends,

I've attached a workbook here. I am trying to calculate ratio here by dividing sum(Duration)/countd(Order Id). While I get the exact unique counts of Order Ids, SUM(Duration) is not giving the desired output. For numerator, ie Sum(Duration) I want the sum of only those records that appear at the first instance against each of the unique Order IDs, and not include for duplicate IDs. I was able to achieve this using index at a row level (used this article..How to sum ONLY the first instance of a value i've not included that in this workbook though), but when I use it at an aggregate level I find it difficult to get the desired output. Any help would be highly appreciated..

Cheers..

Chetan........

• ###### 1. Re: Sum First Instance of a value and use in a calculation

I used both of your dates but your result is not different than me.

• ###### 2. Re: Sum First Instance of a value and use in a calculation

Hi Chetan,

Not exactly sure, but is this what you expect?

I only found "Sales ID" to distinguish the duplication. So used smallest Sale ID as 1st order.

Thanks,

Shin

• ###### 3. Re: Sum First Instance of a value and use in a calculation

Hi

what were the expectations

I looked into the data - where you have multiple records associated with an Order ID at the min date it looks like there are multiple sales ids -

each with the same duration - I think that is throwing you off but I am not sure

In any event first the fixed order date

then the duration calculation-note this is where I used avg()

then the ratio

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Sum First Instance of a value and use in a calculation

Hi Shinichiro,

This looks right.. Appreciate your quick response on this.. Thank you all...

• ###### 5. Re: Sum First Instance of a value and use in a calculation

Hi Chetan

You are very welcome.

Shin