Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
braham
Creator
Creator

First Sorted value not working when calculating a measure

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

Labels (1)
2 Solutions

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

braham
Creator
Creator
Author

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

View solution in original post

2 Replies
hic
Former Employee
Former Employee

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

braham
Creator
Creator
Author

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