How to get Top N customers with other

[vsap2000]

Hi ,

I have searched this forum but can not find the solution for Top N Customers with total for others also.

I have created the straight table which shows Top Revenue Customers for different states( two dimension Customer and State). Even I have created variable for top customer so user enter any number and he gets result, below is expression:

Total Sales:

Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum([Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')

CA State Sales:

Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum({<[Cust State] = {'CA'}>}[Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')

NY State Sales:

Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum({<[Cust State] = {'NY'}>}[Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)')

and more 6 states:

For Eg if user selected top 5 , I get o/p as shown

Cust Name     Total Sales Rev CA Sales Rev  NY Sales Rev

Totals               9000                   4000                     5000

A                         2000                  1000                     1000

B                         1000                  200                        800

C                        1000                  800                        200

D                        3000                 1500                       1500

E                        2000                 500                         1500

but user wants to show remaining other totals also

Other               10000                 3000                             7000

How to achieve last piece Total for OTHER remaining customers using set expression.

I tried also different way but in that I am getting complete column total and others total but not TOP N total.

Any help is appreciated.

Thanks

-V

[Kushal_Chawda]

Is your expression is working fine apart from others?

[vsap2000]

Hi,

Yes, expression works. Say, I want to get TOP 5 Customers, I will see Totals for top 5 Customer how to get remaining customers total at the bottom as shown above?

But if you have other approach please let me know.

Thanks,

-V

[vsap2000]

Is any way you can get total for remaining customers? I am stuck with this requirement, hope to get help.

Thanks.

[Kushal_Chawda]

//create inline table 
Dim:
load * inline [
Dim
1
2 ];

//Create a table
Dimension:
=Pick(Dim,CustName,'Others')

Expression:
=Pick(Dim
Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum([Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)'),
Num(Sum(Aggr(if(rank(sum([Cust Sales Rev]))>$(vTop_Cust),Sum([Cust Sales Rev])), [Cust Name])),'#,##0;(#,##0)'))

// You can use similar expressions for other measure as well

[vsap2000]

Hi,

Thanks for reply, I am not allowed to change anything in Data Model is any way I can get result using set analysis in front end.

Thanks,

-V

[Kushal_Chawda · Accepted solution]

@vsap2000  Sure. Try below

Dimension:

=aggr(if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),[CustName],'Others'),[CustName])

Expressions:

Sum([Cust Sales Rev])

Sum({<[Cust State] = {'CA'}>}[Cust Sales Rev])

Sum({<[Cust State] = {'NA'}>}[Cust Sales Rev])

Sort Expression: (Descending)

if(rank(sum([Cust Sales Rev]))<=$(vTop_Cust),Sum([Cust Sales Rev]),0)

[vsap2000]

Hi Kush,

Thanks for reply and help, now user wants to show total of top customers also i.e vTop_Cust, say user enters 10 and at the bottom it should show Total of 10 customers. Is this achievable?

Appreciate your effort and help.

-V

[Kushal_Chawda]

@vsap2000  Of course it is possible but with my earlier  script approach. Unfortunately you are not allowed to change the script so I am afraid. It was possible with Top 10 and others as condition is based on customer dimension only but with total you are adding another custom factor  which can't  be accommodated in calculated dimension.

[vsap2000]

Hi Kush,

Thanks for reply, it seems I was not clear in question sorry for confusion. User like to see total for top 10 customers in different table, as we can't accommodate both totals together.

Hope this clarifies, please let me know how I can achieve total for top 10 customer.

Thanks again!

-V