Aggr function issue with p()

kkkumar82 — Wed, 08 Apr 2015 08:46:30 GMT

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

Re: Aggr function issue with p()

kkkumar82 — Thu, 09 Apr 2015 08:43:22 GMT

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

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.

topic Aggr function issue with p() in QlikView

Aggr function issue with p()

Re: Aggr function issue with p()