Aggr function issue with p()

[kkkumar82]

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

[kkkumar82 · Accepted solution]

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.