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.

 

[prma7799]

Simply write your expression in dimension

=if(aggr(rank((SUM(Sales))),BY_Region)<=5,BY_Region,null())

Or

Create a variable vTop and put this into dimension limit  and use input box for Top 5

[Anonymous]

Sorry its not working

[prma7799]

Please share your sample application with sample data.

[passionate]

Hi Bijay,

Please find Solution for Drill down:

Refer Chart CH02

Regards,

Pankaj

[Anonymous]

Is there any formula when i can restrict the list to top 5 as per expression.

For Ex:- Aggr(Expression,Dimension)

                =Aggr(Sum(Sales),E_Created_By_User)

                =Rank( Aggr(Sum(Sales),E_Created_By_User))<=5

but still it not working.

[devarasu07]

Hi,

You can try like below,

=IF(Aggr(Rank(SUM(Sales),4),E_Created_By_User)<=5,Aggr(Rank(SUM(Sales),4),E_Created_By_User))

PS: instead of hard coding the Top N Rank (5) value you can use variable (it will be more useful for dynamic top N rank)

Regards,

Deva

[Anonymous]

Sry Pankaj,

I Can't access your Qvw as I am not using licience version.

[Anonymous]

Sorry Devarasu,

the same code is also not working..

Thanks

[devarasu07]

Hi,

Can you share your app, so that will check and assist to you. tks

Note: the expression which i posted earlier will show top 5 based E_Created_By_User level.

Thanks, deva