topic Re: Show Selected plus 'Others' in QlikView

Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 10:22:32 GMT

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

Re: Show Selected plus 'Others'

marcus_sommer — Mon, 17 Aug 2015 11:04:12 GMT

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

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 11:13:26 GMT

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

Re: Show Selected plus 'Others'

marcus_sommer — Mon, 17 Aug 2015 11:21:28 GMT

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

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 11:29:16 GMT

i think problem in brackets..

see the image please,

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

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 11:35:06 GMT

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

Re: Show Selected plus 'Others'

marcus_sommer — Mon, 17 Aug 2015 12:49:55 GMT

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

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 13:23:38 GMT

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

Re: Show Selected plus 'Others'

marcus_sommer — Mon, 17 Aug 2015 13:34:42 GMT

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

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 14:00:02 GMT

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

Re: Show Selected plus 'Others'

marcus_sommer — Mon, 17 Aug 2015 14:15:39 GMT

I have tried to simulate this within an application and for me it looked thats worked:

Re: Show Selected plus 'Others'

israrkhan — Mon, 17 Aug 2015 17:48:09 GMT

it looks like its working for you...

can you please share a sample...?

Re: Show Selected plus 'Others'

israrkhan — Sat, 22 Aug 2015 13:19:39 GMT

Dear Marcus, i see in your image it seems it working for you,

but i tried same , but still no luck...

cam you please have a look on attached sample, in the document, see if i dont select any brand, then i can see top 10 brands and one row for others.

but i i select a brand then i cant see 'other'

please have a look on it...

Many Thanks...

Israr Khan

Re: Show Selected plus 'Others'

marcus_sommer — Sun, 23 Aug 2015 16:39:17 GMT

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