First Sorted value not working when calculating a measure

braham — Mon, 23 Sep 2024 20:56:27 GMT

Evening all,

Once again I need to reach out to the community to produce a chart. I would appreciate your assistance.

I have a header/Detail record structure. What is unusual is that when you add up the values in the detail it does not always equal the value stored in the header. If there are say 4 items in the detail, I want to only show the header value against the 1st item in the detail and show the header value as 0 for the rest as per the table below:

Header	Detail Key	Header Amount	Detail Amount
A	A1	600	196.17
A	A2	0	24.57
A	A3	0	173.87
A	A4	0	97.14

To achieve this, I am using the FirstSortedValue function to return A1 and use an if statement to compare the FirstSortedVale and DetailKey. If they are equal, Sum the Header Value else set it to 0. When using the formulae I get the following table. I have included the first sorted value as a dimension and split the two conditions being compared in the measures to demonstrate what is happening.

<--------------------Dimensions----------------------->			<----------------------------------------Measures--------------------------->
Header	Detail Key	First Sorted Value	First Sorted Value	Detail key	Header Amount	Detail Amount
A	A1	A1	-	A1	0	196.17
A	A2	A1	-	A2	0	24.57
A	A3	A1	A1	A3	0	173.87
A	A4	A1	-	A4	0	97.14

The first sorted value is correct as a dimension, but only populates against the 3rd record in the measure. The If statement returns 0 as all the conditions are not met. The formulae I use is:

If(Aggr(FirstSortedValue(Distinct DetailKey, LineNo), Header) = DetailKey, Sum(Header Amount),0)

I need to understand why the FirstSortedValue in the measures (A1) is placed on line 3 and not on all the lines.

I would appreciate some assistance on this.

Regards

Re: First Sorted value not working when calculating a measure

hic — Mon, 23 Sep 2024 21:08:02 GMT

You have a grain mismatch between the Aggr() and the chart. See https://community.qlik.com/t5/Design/Pitfalls-of-the-Aggr-function/ba-p/1463275

Try using DetailKey as dimension inside the Aggr():

If(
Aggr(FirstSortedValue(Distinct Total <Header> DetailKey, LineNo), Header, DetailKey) = DetailKey,
Sum(Header Amount),
0
)

Or try using ... Aggr(NoDistinct ...) ...

Re: First Sorted value not working when calculating a measure

braham — Tue, 24 Sep 2024 09:41:39 GMT

Morning HIC, Thanks so much for the help, the 'NoDistinct' resolved the problem. I now get the FirstSortedValue on each line and the If statement now works and only shows the summed header value on the first line as I wanted.

I have read a number of your articles, so appreciate your insights into the workings of Qlik.

Regards

Braham

topic First Sorted value not working when calculating a measure in App Development

First Sorted value not working when calculating a measure

Re: First Sorted value not working when calculating a measure

Re: First Sorted value not working when calculating a measure