Top 5 for a dimensin in a Drilldown Group

[Anonymous]

Good Morning Everyone,

I have created a bar chart where

1) Measure : Sum(Sales)

2) Dimension:  1) E_Created_By_System

                      2) BY_Region

                      3) E_Created_By_Users

All the three dimension are gouped under "Dynamic Drill Down" . My question is in a group, how can I restrict the third dimenstion to show only top 5 sales by users.

 

[effinty2112]

Hi Bijay,

Use this expression

=If(GetCurrentField([Dynamic Drill down]) = 'E_Created_By_Users', 5, Count(Distinct $(='[' &GetCurrentField([Dynamic Drill down]) & ']')))

in the dimension limit tab of your chart. Put it in the boxspecifiing the number of values to be shown.

cheers

Andrew

[Anonymous]

Sorry AndrwWalker,

Can you give me a formula where I can show only 5 items in a list box?

[passionate]

HI Bijay,

I have implemented Drill down by some other method:

PFA, Document.

You can set Dimension limit for any respective drill down level..

You can even create list box for your requirement

Please let me know for more information.

[passionate]

The Dimensions and Expressions you can modify as per your requirement

[effinty2112]

Bijay,

This will give you a listbox of the top 5 elements of a dimension, but not in rank order.

=Aggr(If(Rank(Sum(Sales))<=5,Dimension,),Dimension)

have you tried my earlier suggestion which answered your original question?

cheers

Andrew

[Anonymous]

Thanks, Andrew,

But what if, I want to show the list in ascending or descending order.

[balar025]

Hi Bijay,

You can directly add expression inside your drill down group instead of adding field

for example

you want top 5 of A field based on Sales

=if(Aggr(Rank(Sum(Sale)),A)<=5,A)

Same for B,C,D.

instead of static 5 you can use variable with value of listbox of top 5,10,20.

Thanks

Ravi Balar

[effinty2112]

In sort tab sort by expression, sum(sales), descending or ascending.

[Anonymous]

Please use these Gid and try in your own qlik view:

bez i didn't get the answer.

GID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40