Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
kkkumar82
Specialist III
Specialist III

Aggr function issue with p()

Hi all,

I have attached a qvw file below.

In that I have commodities and suppliers and their spend. I have to calculate their share. I got successful in every thing.

one of my requirements is like this.

1. when we select a supplier I want to see the commodities who has that selected supplier + all other 5 suppliers,

in my example you can selected aa or bb.

The commodities and suppliers are visible but share is displayed to only that selected supplier.

Any advice

one more requirement is

2. The suppliers list by default I want to see top 5 suppliers based on the spend , and if I select a supplier

  Selected supplier + other top five suppliers even though the selected supplier is in top 5

Thanks

Kiran Kumar

1 Solution

Accepted Solutions
kkkumar82
Specialist III
Specialist III
Author

At last found the solution for the share expression.

Changed the expression by adding max() function gave me the result what I want.

for Getting the top 5 suppliers I have used calculated dimension as follows

=if(GetSelectedCount(Supplier) > 0,

  if(

  AGGR(RANK(Sum({1-$<Commodity=p(Commodity)>}Spend)),[Commodity],[Supplier])<=5

  OR

  AGGR(RANK(Sum({$<Commodity=p(Commodity) >}Spend)),[Commodity],[Supplier])<=5,

  Supplier)

  ,

  if(AGGR(RANK(Sum({1<Commodity=p(Commodity)>}Spend)),[Commodity],[Supplier])<=5, Supplier)

)

sum({1<Commodity = p(Commodity)>}Spend)

/

max({1<Commodity = p(Commodity)>}aggr(NODISTINCT Sum({1<Commodity = p(Commodity)>}Spend),Commodity))

In the above expression adding max function to the aggr() function gave me the solution and now when I am selecting a supplier it is showing me the selected supplier as well as the other suppliers.

View solution in original post

1 Reply
kkkumar82
Specialist III
Specialist III
Author

At last found the solution for the share expression.

Changed the expression by adding max() function gave me the result what I want.

for Getting the top 5 suppliers I have used calculated dimension as follows

=if(GetSelectedCount(Supplier) > 0,

  if(

  AGGR(RANK(Sum({1-$<Commodity=p(Commodity)>}Spend)),[Commodity],[Supplier])<=5

  OR

  AGGR(RANK(Sum({$<Commodity=p(Commodity) >}Spend)),[Commodity],[Supplier])<=5,

  Supplier)

  ,

  if(AGGR(RANK(Sum({1<Commodity=p(Commodity)>}Spend)),[Commodity],[Supplier])<=5, Supplier)

)

sum({1<Commodity = p(Commodity)>}Spend)

/

max({1<Commodity = p(Commodity)>}aggr(NODISTINCT Sum({1<Commodity = p(Commodity)>}Spend),Commodity))

In the above expression adding max function to the aggr() function gave me the solution and now when I am selecting a supplier it is showing me the selected supplier as well as the other suppliers.