Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
israrkhan
Valued Contributor 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
MVP & Luminary
MVP & Luminary

Re: Show Selected plus 'Others'

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

Re: Show Selected plus 'Others'

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
Valued Contributor II

Re: Show Selected plus 'Others'

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

MVP & Luminary
MVP & Luminary

Re: Show Selected plus 'Others'

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
Valued Contributor II

Re: Show Selected plus 'Others'

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
Valued Contributor II

Re: Show Selected plus 'Others'

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

MVP & Luminary
MVP & Luminary

Re: Show Selected plus 'Others'

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
Valued Contributor II

Re: Show Selected plus 'Others'

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

MVP & Luminary
MVP & Luminary

Re: Show Selected plus 'Others'

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
Valued Contributor II

Re: Show Selected plus 'Others'

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