Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

FirstSortedValue from two sources

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.

37 Replies
JonnyPoole
Employee
Employee

Hi Bradley, yes you need  too sum the results of 1 or more results from firstsortedvalue. I played with your app briefly yesterday but didn't get it to work ...yet.  I think something like this but not sure yet ...   sum(  aggr(   firstsortedvalue(    , mort_dt) ,  <dimensions> ))

JonnyPoole
Employee
Employee

hi brad,

i got back to this.  I am getting numbers now but i'll need your help on accuracy . attached is a sample. screenshot has before/after values .  ( i added after).  please test it out and articulate any issues.

Here is the expression:

= sum(  distinct aggr( noDISTINCT FirstSortedValue( iHeadMort ,Mort_dt), MortHouse, Group_Name))

/ SUM(total <Project_#> Head_Start)

Untitled.png

Anonymous
Not applicable
Author

Jonathan,

The 1.19% is right for Group Cd 4120LR14. When I clear the selections though all the numbers change to the wrong thing. Is this something we can change now that we know this works?

Anonymous
Not applicable
Author

Another note, is that it seems that there is a date selection of 4/24/2014, and when this is unselected the numbers change. Other changes to selections don't seem to have this effect.

Anonymous
Not applicable
Author

4/24/2014 is the first mort_dt for Group_Cd 4120LR14 and so it brings back the correct Week 1 Mort %. When you select another mort_dt it changes the numbers to the mortality for that date. I have been trying to adjust the formula to take into account for this with no luck yet. I am very excited that we are this close to a resolution. Thank you again for your time and effort on this.

JonnyPoole
Employee
Employee

hi brad - i should be able to take another crack tomorrow/thursday.

JonnyPoole
Employee
Employee

Brad - can you tell me what is different about these to project names ? 

4163TH14  and 4120LR14

There is something that distinguishes these two names from others.  Its not the # mort_house because others have multiple mort_houses.  Can you shed any light ?   I'm asking just to save myself some time.

Anonymous
Not applicable
Author

Jonathan,

Each group represents a group of turkeys that we grow through production. For project # 4163TH14 these birds were grown at one grower (THouston) their entire life until they were sold to the production plant. For project # 4120LR14 these birds started at LRoach and were moved around 5 weeks of age to WCW to complete their growing. LRoach is what we call a brooder grower which means their only job is to grow the birds from the time they leave our hatchery until around 5-6 weeks of age when they are moved to a growout grower which in this case is WCW who only grows birds from 5-6 weeks of age until they go to our processing plant. Then we have growers like THouston who grow the birds from the time they are hatched until the time they go to our processing plant. Most of the projects only come from one house at the brooder grower, but their are rare occasions when they come from more than one house. I hope this helps and if you need any more clarification please let me know.

Anonymous
Not applicable
Author

Most of the growers who grow the little birds only have one house # in the first five weeks, but there are some exceptions on a few larger farms. That is why most of the projects pull in the correct value for week one. Would some kind of IF statement help?

Anonymous
Not applicable
Author

Jonathan,

Any update?