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.

[Anonymous]

Deepak,

So were you able to open the test file and use this expression to return the Week 1 Mort % for the group cd: 4120LR14 that was currently missing?

[JonnyPoole]

Hi Bradley,  I looked at your chart but based on your data model i see an issue with the following description:

"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."

iHeadMort comes from the GroupMortality table and the numbers are associateed to the rest of the data model on 'Group Key ID' .

'Group Key ID'  will associate to multiple values of  'Breeder Group' through a linked table .From there, each combination will associated to one or more records in the 'Group Placements' table.  House_# is in this Group placements table and there may be several records for each house and also several houses.

So basically iHeadMort seems to be reported at a higher level than House_# in a separate table, so if I have an iHeadMort value of 100 ,  i'm not sure i understand how to come up with broken down value for each house. Even if you are only asking for the oldest value based on Mort_dt , that single record from the GroupMortality table may associate to  multiple house_#'s from the 'Group Placements' table

Its sort of like reporting the population of a continent , and then asking qlik for an expression that calculates the country... you can't necause the data doesn't have the populations reported at the country level  ...and so the question only works if there is only 1 country in a  continent.

I'm not sure what your source data says but you may need to create a data model that has both house and iHeadMort in the same table or at least iHeadMort reported at the same or lower level of granularity as 'house_#'.

Am i way off ?    i spent a bit of time looking at it and i'm a bit cross eyed.

[IAMDV]

Hi,

I'm getting results for [Week 1 Mort%]. But I'm not sure if they are expected results. See the attached screenshot and following expression. I can see the values for [Group Cd] field, however for the rows 4120LR14 you're getting same numbers even after aggregating the data hence you'll see two NULL values. I've checked these values and both of them have same dimension values and same expression result values, hence FirstSortedValue() with Aggr() function still returns NULL values for these two records. But rest everything seems to be working. Let me know if this what you need?

Expression:

FirstSortedValue(House_#, Aggr( SUM(Mort_dt), House_#, sSvcman_cd, Project_#, Group_Name, Hatch_Date, Place_Date, Breed, Sex, Condition_Code, Breeder_Group))

/

SUM(total <House_#, sSvcman_cd, Project_#, Group_Name, Hatch_Date, Place_Date, Breed, Sex, Condition_Code, Breeder_Group> Head_Start)

Cheers,

DV

www.QlikShare.com

[Anonymous]

Thanks guys for your help. I hope to be just like you when I grow up . Thank you Jonathan for your time spent digging into the root of the problem, and thank you Deppak for never giving up on the correct answer. I will look at another way to load the information if this becomes a big problem. For now it seems that this problem is not correctable with the current state of the data. Again, I thank you for your time and wisdom on this problem.

[JonnyPoole]

Thank you Brad.

[IAMDV]

That's great Brad. In fact, I like working on these kind of issues. I wish you good luck!

Jonathan - You're one of few people on community, who cares to give not just the correct answer but also helpful & friendly response with clear explanation instead of just aiming at the points. Thank you for your all contributions on the community.

Cheers,

DV

www.QlikShare.com

[JonnyPoole]

cheers deepak!  i can see Bradley put together some effort to create a sample so the least i can do is the same.  i thought i knew a fair amount of QV prior to 'getting active' on community but the range of solutions from yourself and others that i see on this forum honestly has me back to learning things every day which i did not expect... but that is the nature of qlikview so i should not be surprised

[Anonymous]

Guys,

I just spoke with one of my coworkers here and showed them Jonathan's explanation and he said that I am not looking to group from House_# that is in the placement table, but from MortHouse that comes from the mortality table. would this help make a difference in the levels Jonathan mentioned?

[JonnyPoole]

It definitely should. Thats the same table where the other 2 fields come from so we should be good on the granularity...  stay tuned

[Anonymous]

So what I need now is some way to add the amount from MortHouse 1 & 2 together but still only retrieve the FirstSortedValue for each. Do I need some sort of Aggr function as Deepak mentioned earlier, or is there another way to total these?