Firstsortedvalue (Grand Total) issue

[Anonymous]

Hi Guys,

How do I sum the resultant of this expression to display in a textbox?

=FirstSortedValue(Aggr(Sum(FIELDNAME1),FIELDNAME2), -FIELDNAME2).

Thanks

[Anonymous]

Its just a regular text object.

It displays when a reference number is select but I prefer show the total at a glance without a selection

[tresesco]

Yes, it doesn't work like this. However, it is also true that you can use TOTAL qualifier for nested aggregation, like said here: https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/NestedAggregation...

Therefore, nesting of aggregation functions can be done using aggr() and total qualifier but in certain scenarios (I am not sure how to generalize this for better distinction). 

[sunny_talwar]

So you want sum by Reference Number, right? May be this

=Sum(Aggr(FirstSortedValue(Aggr(Sum(FIELDNAME1),FIELDNAME2), -FIELDNAME2), [Reference Number]))

or

=Sum(Aggr(FirstSortedValue(Aggr(Sum(FIELDNAME1), FIELDNAME2, [Reference Number]), -Aggr(FIELDNAME2, FIELDNAME2, [Reference Number])), [Reference Number]))

[sunny_talwar · Accepted solution]

It seems that if it is done in the inner aggregation, it works

[tresesco]

Yes, that is what I missed. This makes sense now and I get a generalization. Thanks Sunny.

[sunny_talwar]

No, thank you for taking time to refer the help text.

[Anonymous]

Essentially Fieldname2 is an endorsement count on the number of times the records gets modified.

So my requirement is to only pick the max count and sum its corresponding amount to display i.e.only the third record with ;3  with reference to the example before.

PA0001;1

PA0001;2

PA0001;3

NB: Reference numbers with only one endorsement count just gets added straight forward.

[sunny_talwar]

May be this

Sum(Aggr(Max(FieldName2), Fieldname1))

or

Sum(Aggr(Max(FieldName1), Fieldname2))

Not sure which one is FieldName1 and which one is FieldName2...

[Anonymous]

FieldName1 = Amount

FieldName2 = Endorsement Count

[sunny_talwar]

What is PA0001 then?