Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Below is the table i am using
Customer name | Sale |
A1 | 938 |
A2 | 324 |
A3 | 302 |
A4 | 463 |
A5 | 427 |
A6 | 0 |
A7 | 277 |
A8 | 387 |
A9 | 0 |
A10 | 183 |
i need create two tables for top 5 and bottom 5 . In bottom 5 i need to exclude whose customer having "zero" in their sales . User is flexible to select any top n or bottom n b/w 5 (i.e., top 1-2 or bottom 2-3 or top 3-4) .
Could any one help me .
Thanks in advance
You can add a range to the condition like
=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)
Create two tables, Customer name being dimension in both.
Then as expression
=Sum({<CustomerName = {"Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)
resp.
=Sum({<CustomerName = {"Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)
Hi Stefan,
Thanks for reply..
could you kindly provide me the expressions for $(vTopRank) and $(vBottomRank).
But however, the above expression helps me to filter out the data according to top or bottom N. but my requirement is if i wanna to see only top 2 and 3 customer sales or bottom 2 and 3 customer sales .
Thanks in advance
vTopRank and vBottomRank are just variables that hold a number (Top or Bottom N customers).
I thought that's what you intended (creating e.g. a slider so the user can select N)
I've also noticed I forgot the leading equal sign in the set modifier to indicate an advanced search:
=Sum({<CustomerName = {"=Rank(Sum(Sales))<=$(vTopRank)"}>}Sales)
resp.
=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(vBottomRank)"}>} Sales)
Hi stefan,
Thanks for previous reply..
But when i using ur bottom expression(for bottom 4 customer) . the output is show below
but the user wanna to only bottom 2 to 4 records
that is, he wanna to see only
Kindly help me ..
Thanks in advance
You can add a range to the condition like
=Sum({<CustomerName = {"=Rank(-Sum({<Sales = {'>0'}>} Sales))<=$(Max) and Rank(-Sum({<Sales = {'>0'}>} Sales))>=$(Min)"}>} Sales)
hi,
Thanks a lot ...
Hi
If we want to see along with zero values for bottom customers ?
i have removed >0 from the set analysis but i m not getting correct values .
Thanks in advance
Hi ,
i have a varable
vProdVar= sum(aggr(if(IsNull(Map_Customername) or not( IsNull(Map_Key)),'',TotalMargin),Key,[customer name]))+sum(aggr(if(IsNull(Mapping_Customername) or not( IsNull(Mapping_Key)),'',[Total margin]*-1),Key,[customer name]))
in straight table .. i m using the below dimension for selecting bottom customers.
AGGR(IF(Rank(-$(vProdVar),4)>=$(vMin) AND
(Rank(-$(vProdVar),4)<=$(vMax)) ,[customer name] ),[customer name] )
i am getting error with rank function . Could you kindly help me ..
Thanks advance
Not sure what your complex variable should return.
Could you post a small sample QVW that demonstrates also your data model?