Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
How do I sum the resultant of this expression to display in a textbox?
=FirstSortedValue(Aggr(Sum(FIELDNAME1),FIELDNAME2), -FIELDNAME2).
Thanks
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
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).
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]))
It seems that if it is done in the inner aggregation, it works
Yes, that is what I missed. This makes sense now and I get a generalization. Thanks Sunny.
No, thank you for taking time to refer the help text.
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.
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...
FieldName1 = Amount
FieldName2 = Endorsement Count
What is PA0001 then?