Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ...) ...
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
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 ...) ...
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