Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
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:

CustomerProductProduct TypeUnitsValue
Dean LtdZZZ12310100
Dean LtdXXX34535350
Dean LtdZZZ12320200
Carl ConstructionZZZ12330300
Carl ConstructionXXX34525250

 

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

CustomerProductProduct TypeValueHighest Sold Product
Dean LtdZZZ123300XXX
Dean LtdXXX345350XXX
Carl ConstructionZZZ123300ZZZ
Carl ConstructionXXX345250ZZZ

 

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

Thanks,

Dean

Labels (3)
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))

View solution in original post

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

You can try this

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

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.

CustomerProductProduct TypeUnitsValue
Dean LtdZZZ12310151
Dean LtdXXX34535350
Dean LtdZZZ12320200
Carl ConstructionZZZ12330300
Carl ConstructionXXX34525250
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))

View solution in original post

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

Thanks, this seems to work perfectly.