FirstSortedValue from two sources

[Anonymous]

I have a formula that uses the FirstSortedValue function that works fine as long as there is only one house that the iHeadMort comes from, but if it comes from two houses then I need some way to add the two amounts together.

=FirstSortedValue(iHeadMort,Mort_dt)

I tried something like this, but it did not work:

=FirstSortedValue(SUM(iHeadMort),Mort_dt)

Any ideas will be greatly appreciated.

[JonnyPoole]

Brad - i did play with the expressions some more shortly after my last note but was not successful. I was going to take a look at the data model to try understand why the difficulty in getting the right effect but never got back to it. I'll see if I can get back into it this week.

[JonnyPoole]

Delving into this again and looking into the scenario when there is more than one mort_house. When that happens, each mort_house has several mort_dates.

So looking at the following group code,   firstsortedvalue( iheadmort, mort_dt)  results in  .6%

This is because , when you look at all the data,  the lowest mort_dt is 3/27/2008 and the value was also .6%.

But what about the other MortHouses ?  In the list .6% came from mort_house 004... but what do you want to do about 005 and 006 and all the rest ?   

I'm trying to understand how those should be aggregated based on the previous explanations . If you notice below my new attempt is capturing the first mort_dt value for each mort_house (just 2 showing) and then when i add up these percentages i get 11.02 above ... but is that correct ?   Or is the first value .6% correct ?

Can you let me know what is the expected result ?

[Anonymous]

Ok, what is happening here is that for the first six weeks this flock of birds was in one house(4) then they grew to big to be in one house so they were moved to house 5 and 6. The .60% is correct because the birds were only in one house. Only the first week matters for this calculation, and the problem comes in when the birds are first placed at a farm that is large enough to house 2 houses of birds on the first week. They should have the same mort date, but there are two separate mortality amounts from each house. Hope this helps clarify, and thank you for not giving up on this. I know it seems like a lot of work for just this small detail, but this crucial to replacing a report that is currently generated using an excel spreadsheet.

[JonnyPoole]

Okay.

So only if the first sorted mort dt appears on 2 houses do i sum the 2 percentages together ?

So for arguments sake lets say the data in the screenshot above had house 004 and 005 both with values for 3/27/2008 which is the earliest date.  Would i then  sum .6% and .22% to get .82% ? 

[Anonymous]

Almost, if both houses had values you would need to add both values together and divide by the total head start to get the correct amount. I don't know that adding the percentages together would give you the same answer, but it might.

[JonnyPoole · Accepted solution]

Ok. Thanks.  Banged my head on this for another 90 mins.  Found out that i can pass an aggr() to firstsortedvalue as long as i use DISTINCT in firstsortedvalue which is undocumented and the expression editor flags but yet appears to work nonetheless.  This means i can aggregate  the numerator on date (so if multiple morthouses it sums as long as the date is the same) , and then take the aggregate associated with the lowest date, which is what i am aiming for.  Even if it doensn't work i have just learnt something.

Please go ahead and QA the attached to see if you can find any problems.

Here is the overall  update expression:

FirstSortedValue( total <Breeder_Group, Project_#>  Distinct aggr(sum(  iHeadMort), Project_#,Breeder_Group,Mort_dt) , Mort_dt)

/

SUM(total <Project_#> Head_Start)

[Anonymous]

Jonathan,

From everything I can tell it works perfectly. I will have the person who runs this report regularly go over it, but I think we are good to go. Thank you again for your efforts on this.

Thanks,

Bradley Faircloth

[JonnyPoole]

That is good to hear. Keep me posted. This was my last 'biggie' on my list (for now).