# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for
Did you mean:
Highlighted
Creator

## Highest Sold Product calculated by aggr value for each Customer

I've been going around in circles a little bit with firstsortedvalue() and aggr() to try to achieve below:

My data is a little like this:

 Customer Product Product Type Units Value Dean Ltd ZZZ 123 10 100 Dean Ltd XXX 345 35 350 Dean Ltd ZZZ 123 20 200 Carl Construction ZZZ 123 30 300 Carl Construction XXX 345 25 250

And I need the result to be like this in my pivot table:

 Customer Product Product Type Value Highest Sold Product Dean Ltd ZZZ 123 300 XXX Dean Ltd XXX 345 350 XXX Carl Construction ZZZ 123 300 ZZZ Carl Construction XXX 345 250 ZZZ

It's the highest sold product field I would appreciate any help with please?

Thanks,

Dean

Labels (3)

• ### highest sales

1 Solution

Accepted Solutions
Highlighted
Anonymous
Not applicable

Okay, I misunderstood that you are looking for highest value, not for highest sum

Now i changed the expression and made some tests and for me it looks good

can you make further tests?

= FirstSortedValue(total <Company> Product, -aggr(sum( total <Company,Product> Val),Company,Product))

6 Replies
Highlighted
Anonymous
Not applicable

it was a hard way to find (maybe) the solution. At least with your data I am able to produce your desired output table

I used firstsortedvalue for your desired column

= FirstSortedValue(total <Company> Product, -Val)

I hope that this works not oly with your 5 rows of data. Please try!

Highlighted
MVP

You can try this

``````Concat(DISTINCT TOTAL <Customer> Aggr(
If(Sum(Value) = Max(TOTAL <Customer> Aggr(Sum(Value), Customer, Product)),  Product)
, Customer, Product))``````
Highlighted
MVP

I think this was only by chance that this worked. If you change the Value for first row to 151 from 100.. you will see that the expression that you have mentioned will continue to show XXX as the most valuable product, but now ZZZ is 351. So, it should be ZZZ as most valuable product for Customer Dean Ltd based on the new data.

 Customer Product Product Type Units Value Dean Ltd ZZZ 123 10 151 Dean Ltd XXX 345 35 350 Dean Ltd ZZZ 123 20 200 Carl Construction ZZZ 123 30 300 Carl Construction XXX 345 25 250
Highlighted
Anonymous
Not applicable

Okay, I misunderstood that you are looking for highest value, not for highest sum

Now i changed the expression and made some tests and for me it looks good

can you make further tests?

= FirstSortedValue(total <Company> Product, -aggr(sum( total <Company,Product> Val),Company,Product))

Highlighted
Creator

Thanks, this was close, it works for like 95% of my data

Highlighted
Creator

Thanks, this seems to work perfectly.