Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist II
Specialist II

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist II
Specialist II

i think problem in brackets..

see the image please,

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

111.png

Highlighted
Specialist II
Specialist II

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist II
Specialist II

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist II
Specialist II

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