Expression to get Main field for each attribute

[Saurabh07]

Hi,

I have mentioned structure of data source table;

Company_Name	ID	Country	Product	Value
Company A	1234	Japan	BL_1	75
Company A	1234	Japan	BL_2	125
Company A	1234	Japan	BL_3	100
Company B	5678	India	BL_4	50
Company B	5678	India	BL_3	25

In my  Qlik report I am trying to develop an expression using set analysis to get Main product, ie. Product with highest share of Value for each Company.

Company_Name	ID	Segment	Total Value	Main Product
Company A	1234	Segment 1	300	BL_2 (42%)
Company B	1234	Segment 2	75	BL_4 (67%)

To acheive this, I am using the mentioned set expression ;

=Value / aggr(sum(Value),Company_Name)

But is giving the share of all the products. How do I get only the Main Product to be displayed? I believe I need to use Rank function, but cant figure out how?

Can anyone help me out with this please?

[Taoufiq_Zarra · Accepted solution]

@Saurabh07  are you looking for :

Input data :

Dimenstion: Company name and ID

Expression :

for Total Value :

=sum(total <Company_Name> Value)

and Product main:

=FirstSortedValue(Product,-aggr(sum(Value),Company_Name,Product))

value main product:

=Num(Max(aggr(Max(Value),Company_Name,Product))/sum(total <Company_Name> Value),'# ##0%')

so you can combine values as :

=FirstSortedValue(Product,-aggr(sum(Value),Company_Name,Product))&' ('&Num(Max(aggr(Max(Value),Company_Name,Product))/sum(total <Company_Name> Value),'# ##0%')&')' 

output:

[Saurabh07]

@Taoufiq_Zarra This worked like magic. Awesome! Thanks a ton! So was the trick with the FirstSortedValue function instead of Rank? and why do we put - sign before aggr? It would be great help if you help me understand this function!!!!

Thank you again!

[Taoufiq_Zarra]

@Saurabh07  FirstSortedValue() returns the value from the expression taking into account rank (- for the max )

you can read this article

[Saurabh07]

@Taoufiq_Zarra  Great, thanks!!