Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cypressyg
Contributor
Contributor

Retrieve the dimension based on the max of measure

Hello there,

I have struggled with this Qlik Sense chart expression for a few days and have not figured out how to write it. Hope you can help me: )

I have a data table concatenating data set A  - revenue and data set B - opportunity. They both have 2 common fields - customer and part_number. I created a table that has customer and part_number as dimension, and a measure for revenue. I want grab a new dimension from data set B where it corresponda to the max of another measure for the same customer and part_number (sum({<$(vSetfromB)>}$(vValuefromB)) ). So below is the expression I wrote but only got null values:


FirstSortedValue(dim_from_B, -aggr(sum({<$(vSetfromB)>}$(vValuefromB)), customer, part_number, dim_from_B) )


Could you let me know where is wrong? Thanks a lot!

1 Solution

Accepted Solutions
cypressyg
Contributor
Contributor
Author

This works:

FIRSTSORTEDVALUE(TOTAL <customer, part_number> dim_from_B, AGGR(SUM({<$(vSetfromB)>}$(vValuefromB)), customer, part_number, dim_from_B))

Thanks for the previous reply from stalwar1!

View solution in original post

2 Replies
sunny_talwar

Don't see anything which looks wrong in the first look... would you be able to share  a sample where this isn't working....

also, you can try to add DISTINCT to see if that helps

FirstSortedValue(DISTINCT dim_from_B, -aggr(sum({<$(vSetfromB)>}$(vValuefromB)), customer, part_number,dim_from_B) )

cypressyg
Contributor
Contributor
Author

This works:

FIRSTSORTEDVALUE(TOTAL <customer, part_number> dim_from_B, AGGR(SUM({<$(vSetfromB)>}$(vValuefromB)), customer, part_number, dim_from_B))

Thanks for the previous reply from stalwar1!