Indirect set analysis,calculated dimension,aggr() function issue

[kkkumar82]

Hi all,

I have a requirement to create a pivot table where I need to have two dimensions

1. Commodity

2. Supplier

Measures

1. Market Share

2. Savings

The data is in such a way that a supplier may be present in other commodities also and I have a filter supplier.

Initially the table should show top 5 suppliers per commodity in the table along with the market share of supplier on that commodity.

If any supplier is selected , the table has to show selected supplier + top 5 if the selected supplier is not in top 5 , otherwise selected supplier + 4.

for this I used a calculated dimension for supplier to get the top 5 suppliers when selected or not selected as follows(correct me If I am wrong)

if(GetSelectedCount(Supplier) > 0,

  if(AGGR(RANK(Sum({1-$<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2),      
     $(vLastQuarter3)}>}Spend)),[Commodity],[Supplier])<=5

  OR

  AGGR(RANK(Sum({$<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2),  
    $(vLastQuarter3)}>}Spend)),[Commodity],[Supplier])<=5, Supplier)

  ,

  if(AGGR(RANK(Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend)),[Commodity],[Supplier])<=5, Supplier)

  )

Initially I was not able to get market percentages correctly per commodity and I used the following post to get it

Quick Development Tips: Qlikview: Aggregation 1: How to calculate percentages at second dimension le...

but the market share is showing correctly when supplier filter is not selected but when I am selecting a supplier it is showing market share for selected supplier and not showing the other top 5 or top 4(showing dashes for other suppliers market share).

The expression for market share is as follows

Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend)

  /

Aggr(NODISTINCT Sum({1<Commodity=p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),Commodity)

I have used p() because if this supplier is part of another commodity I also want to get that commodity with its suppliers.

Any advice

Thanks

Kiran Kumar

[kkkumar82 · Accepted solution]

Got the solution

added max() function to the aggr() function in market share expression as

Sum({1<Commodity = p(Commodity),QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend)

/

Max({1<Commodity = p(Commodity)>} aggr( NODISTINCT Sum({1<Commodity = p(Commodity), QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),Commodity))