topic Re: Firstsortedvalue (Grand Total) issue in QlikView

Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 11:59:42 GMT

Hi Guys,

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

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

Thanks

Re: Firstsortedvalue (Grand Total) issue

tresB — Tue, 27 Feb 2018 12:07:38 GMT

May be this?

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

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:10:43 GMT

Will that work without the Aggr() Tresesco?

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:11:32 GMT

Shouldn't it be this

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

Re: Firstsortedvalue (Grand Total) issue

tresB — Tue, 27 Feb 2018 12:11:40 GMT

It should, because TOTAL is there.

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:12:29 GMT

I did not know that by using TOTAL you can avoid using an Aggr. I will try to test this out. Thanks

Re: Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 12:14:43 GMT

Tried something quite similar "Nested aggregation"

Re: Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 12:16:24 GMT

That expression return "0"

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:18:22 GMT

For some reason, it did not work for me

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:18:53 GMT

Would you be able to share an image of the chart where you are using this?

Re: Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 12:32:12 GMT

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

Re: Firstsortedvalue (Grand Total) issue

tresB — Tue, 27 Feb 2018 12:33:58 GMT

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/NestedAggregations/nested-aggregation…

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

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:35:14 GMT

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

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

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

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:37:54 GMT

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

Re: Firstsortedvalue (Grand Total) issue

tresB — Tue, 27 Feb 2018 12:41:44 GMT

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

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 12:44:14 GMT

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

Re: Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 12:53:15 GMT

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.

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 13:00:40 GMT

May be this

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

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

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

Re: Firstsortedvalue (Grand Total) issue

Anonymous — Tue, 27 Feb 2018 13:08:32 GMT

FieldName1 = Amount

FieldName2 = Endorsement Count

Re: Firstsortedvalue (Grand Total) issue

sunny_talwar — Tue, 27 Feb 2018 13:09:23 GMT

What is PA0001 then?