Solved: FirstSortedValue from two sources - Qlik Community

Anonymous · ‎2014-09-05

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 · ‎2014-10-01

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)

View solution in original post

JonnyPoole · ‎2014-09-05

Is this for a chart ?

You can use the same chart aggregation syntax with firstsortedvalue. Assuming House is a field in the data model try this:

FirstSortedValue(TOTAL <House> iHeadMort ,Mort_dt)

it will bring the first value of iHeadMort within each grouping of House.

Anonymous · ‎2014-09-05

Jonathan,

It is for a table. That sounds like it would work, but it didn't. I think its on the right track though.

Report Inappropriate Content · ‎2014-09-05

If it is for a back end table I would try a Group by.

Thank you

Suraj

JonnyPoole · ‎2014-09-05

OK. Can you share a sample qvw ?

IAMDV · ‎2014-09-06

Hi Bradley,

You need something like this:

=FirstSortedValue(iHeadMort, Aggr(SUM(Mort_dt),iHeadMort))

FirstSortedValue() functions supports nested aggregation using the Aggr() function. Also, I'd recommend you to watch below video post on my blog.

http://qlikshare.com/tag/firstsortedvalue/

Cheers,

DV

www.QlikShare.com

Anonymous · ‎2014-09-08

Here is the table and a screenshot. I tried the Aggr formula from Deepak with no success. I think we are close to the solution and this is the last obstacle before this table can be published. Please help.

JonnyPoole · ‎2014-09-08

Bradley - can you tell me which field is your 'house' field ? Also I assume the expression that is incorrect is 'Week 1 Mort %' ?

Also the above approach isn't relevant because you need a combination of SUM and firstsortedvalue. The solution will likely be akin to what Deepak wrote below ...just need some more details of your model.

Anonymous · ‎2014-09-08

The field is house_# and yes Week 1 Mort % is the incorrect expression.

IAMDV · ‎2014-09-08

Hi Bradley,

I assume that you wanted to use this expression in the Pivot Table. If yes, have you tried the following expression:

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

PS: Add one more missing dimension in the above expression which is there in your chart [S E X]. If I include in the expression it requires moderation hence I'm writing this message seperately.

It works for me!

If you need to use the expression elsewhere please let me know which chart/sheet.

Cheers,

DV

www.QlikShare.com