Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
israrkhan
Specialist II
Specialist II

Show Selected plus 'Others'

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...

111.png

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

112.png

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

1 Solution

Accepted Solutions
marcus_sommer

Untill a proper sorting (which could be quite difficult within pivot-tables especially with calculated dimensions and a first try with dual() didn't work - but I think with some more efforts it should work, too - see for example: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)) should it work now.

- Marcus

View solution in original post

13 Replies
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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

i think problem in brackets..

see the image please,

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

111.png

israrkhan
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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