topic Expression to get Main field for each attribute in App Development

Expression to get Main field for each attribute

Saurabh07 — Mon, 20 Dec 2021 21:08:46 GMT

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?

Re: Expression to get Main field for each attribute

Taoufiq_Zarra — Thu, 08 Oct 2020 10:21:04 GMT

@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:

Re: Expression to get Main field for each attribute

Saurabh07 — Thu, 08 Oct 2020 10:47:23 GMT

@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!

Re: Expression to get Main field for each attribute

Taoufiq_Zarra — Thu, 08 Oct 2020 10:52:14 GMT

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

you can read this article

Re: Expression to get Main field for each attribute

Saurabh07 — Thu, 08 Oct 2020 11:03:04 GMT

@Taoufiq_Zarra Great, thanks!!