Show Selected plus 'Others'

[israrkhan]

Hi Community,

i have a pivot table, with two dimensions and one expressions..

my dimensions are:

1) =If(Aggr(Rank(Sum(SellOut.QTY)), Product)<=10, Product, 'Others')              // to show top 10 Product default

2) =Aggr(if(YearWeek > (max( TOTAL YearWeek) - 8), YearWeek), YearWeek)  // to show last 8 week default.

i have one expression...

1)  =if(Dimensionality()=0,SUM({<Product=>}SellOut.QTY),Sum(SellOut.QTY))

// to show the full total all the time, even if user has selected the products.

finally i see this graph.. which is correct...

now if i make selection in Product list box, let say i chose 5 Products...the chart look like below...

everything is ok, but only 'Others' row get disappear...

i want to show Selected and 'others' aswell..

how to show other all the time... all the time selected plus 'others' as a row..

i think its clear...

Many Thanks

[marcus_sommer]

You need to include a set analysis within your calculated dimension to force the displaying of Others, maybe in this way:

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<=

     if(getselectedcount(Product) < 10, if(getselectedcount(Product), 10), Product, 'Others')

- Marcus

[israrkhan]

Thanks Marcus..

there is an error in expression

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<= if(getselectedcount(Product) < 10, if(getselectedcount(Product), 10), Product, 'Others')

trying to resolve error...

will let you know, if it works..

[marcus_sommer]

Maybe it's not accepting directly an expression by the selection-count query and it needs wrapped in $-sign expansion:

... $(=if(getselectedcount(Product) < 10, if(getselectedcount(Product), 10)) ...

- Marcus

[israrkhan]

i think problem in brackets..

see the image please,

and i could not understand the logic of text covered in red box...

[israrkhan]

these both are ok, but not showing 'Others'

1) =If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<= if(getselectedcount(Product) < 10, getselectedcount(Product),10), Product, 'Others')

2) =If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<= if(getselectedcount(Product) < 10, getselectedcount(Product)), Product, 'Others')

showing only selected...as before

[marcus_sommer]

I think my first suggestions was unnecessary complex and not quit suitable and think it's better to leave the calculated dimension like it was and extend the expression with set analysis of 1:

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<=10, Product, 'Others')

= Sum({1 < Week = p(Week)>} SellOut.QTY)

- Marcus

[israrkhan]

i think first expression will be like

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<=10, Sum SellOut.QTY), 'Others')

instead of

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<=10, Product, 'Others')

but this will show 'others' in the cells, not as a row.

and where to use second expression...?

     Many thanks

[marcus_sommer]

Sorry I wasn't accurate enough:

calculated dimension:

If(Aggr(Rank(Sum({1} SellOut.QTY)), Product)<=10, Product, 'Others')

expression:

= Sum({1 < Week = p(Week)>} SellOut.QTY)

- Marcus

[israrkhan]

if i use Sum({1<YearWeek = p(YearWeek)>} SellOut.QTY))

then i am not able to select Product, if i select Product the chart does not change because of 1.

it show all the time 10 product and 'Others' as it was.

but if i use Sum({<YearWeek = p(YearWeek)>} SellOut.QTY))

without 1 ,and if i select few Product,  it shows only selected Products...  no 'Others'

i think it will not work, even i tried many possibilities..but no luck

Really i appreciate your time...

Many Thanks